Mid function query

[VBABEGINER]

Pls Note: The following query answer i got from one of our MrExcel Member, but this is some applicable to limited no. of result, hence query asking again.

The solution I got that is-

=MID(F12,1,FIND(".",F12)-1)&MID(F12,FIND(" ",F12),FIND(".",F12,FIND(" ",F12))-FIND(" ",F12))

My query was, i have data like,
Tag No.
P01.SG-17 P01B.SG-18
P01.SG-17 P01A.SG-25
P05.SG-21 P05A.SG-22
P02.SG-11
P02.SG-12
P07.SG-12 P07A.SG-15 P07B.SG-16

O/p should be -
P01 P01B
P01 P01A
P05 P05A
P02
P02
P07 P07A P07B

The reason i red marked, the formula is fixed. That made for 2 naming convention only. Not Single ("P02.SG-11") or for three content.
That is, if set this much only-

=MID(F12,1,FIND(".",F12)-1)&MID(F12,FIND(" ",F12))

then this will applicable to only single naming convetions.

I dont want this.
tag no. can be any length of content. Only SG tag dont want to display.
Tha is from 1st "." to space and like way.

Can any one there to SOLVE problem?

I am also trying some R&D, by using SUBSTITUTE function.

 

[p45cal]

A custom user defined function perhaps?
=StripTag(A1)
backed up by a function:

Function StripTag(r As String) As String
With CreateObject("vbscript.regexp")
  .Pattern = "\.[A-Za-z]+\-[0-9]+"
  Do
    X = Len(r)
    r = Trim(.Replace(r, ""))
  Loop Until Len(r) = X
End With
StripTag = r
End Function

Now I'm not familiar with regex, and I don't know how to search for all instances of a pattern in a string using it, hence the loop.
The pattern that this one looks for is:
a dot, then a sequence of consecutive alpha characters, then a hyphen, then a sequence of consecutive numerals.
I'm sure it could be better.

Excel Workbook
	A	B
9	P01.SG-172 P01B.SG-18	P01 P01B
10	P01.SG-17 P01A.SG-25	P01 P01A
11	P05.Sg-21 P05A.SG-22	P05 P05A
12	P02.SG-11	P02
13	P02.SG-12	P02
14	P07.SG-12 P07A.SG-15 P07B.SG-16	P07 P07A P07B
Sheet

 

[VBABEGINER]

I am really thankful by so much kind attention.

I had applied the same as ur advised.
in cell b1 it looks like this - #Name?

even i am not got this, what xctly this?

in backend i simply copy and paste this code.
then in each cells in Col b I wrote =StripTag(A1)
but nothing.

It helps mi great, if pls help for this.

 

[niceguy_jay]

A custom user defined function perhaps?
=StripTag(A1)
backed up by a function:

Function StripTag(r As String) As String
With CreateObject("vbscript.regexp")
  .Pattern = "\.[A-Za-z]+\-[0-9]+"
  Do
    X = Len(r)
    r = Trim(.Replace(r, ""))
  Loop Until Len(r) = X
End With
StripTag = r
End Function

Now I'm not familiar with regex, and I don't know how to search for all instances of a pattern in a string using it, hence the loop.
The pattern that this one looks for is:
a dot, then a sequence of consecutive alpha characters, then a hyphen, then a sequence of consecutive numerals.
I'm sure it could be better.

Excel Workbook
	A	B
9	P01.SG-172 P01B.SG-18	P01 P01B
10	P01.SG-17 P01A.SG-25	P01 P01A
11	P05.Sg-21 P05A.SG-22	P05 P05A
12	P02.SG-11	P02
13	P02.SG-12	P02
14	P07.SG-12 P07A.SG-15 P07B.SG-16	P07 P07A P07B
Sheet

I was working on this and thought of checking back this post and found out this AMAZING ( ) solution already posted...

couldn't resist to leave a feed back...


Thanks for the share of this new code (atleast for me)...

 

[p45cal]

in backend i simply copy and paste this code.

backend?
Paste the code into a standard code module of the same workbook.

 

[p45cal]

I was working on this and thought of checking back this post and found out this AMAZING ( ) solution already posted...

couldn't resist to leave a feed back...


Thanks for the share of this new code (atleast for me)...

Thank you for the feed back!

 

[VBABEGINER]

Pls guys, help me.
Not working at my end. Kindly tell me the solution. I also wanted to enjoy this trick lik u guys.

 

[p45cal]

Pls guys, help me.
Not working at my end. Kindly tell me the solution. I also wanted to enjoy this trick lik u guys.

Paste the code into a standard code module of the same workbook.

what more can I say?

 

[VBABEGINER]

Let me tell you what did.

 

[niceguy_jay]

On the workbook press Alt+F11
Insert a new module and paste the code mentioned...

Then in the workbook in the cell B1 this formula..
=StripTag(A1)
and copy paste this formula till the end of column B for which you want the values