Extract text after specific char in a string

[WmK]

HI
Probably simple I just cant figure it out.

I have cell content of a variable length string and I need to extract all text after "///" an example is below:

897/C///Amarnath Leena CD20110316 09105348

I need to pull everything after the "///" Another example would be: 744/I/373632///SUVARNA, VINOD K MD=20110926 231597 Again I need to pull everything after the "///" Right, Left, and Mid wont work since the position is different any thoughts? <tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

...that said, I got with Rick’s above formula for:

F10005537 100 % Polyester 78gsm2 Woven

this:

F10005537 100% Polyester

This formula otherwise can handle „Lambs wool” in middle position, but can not in end position (see the example with red question marks created in post #47).

Correct... if you have a multi-word material at the end, then as you said, there is no way to know if the second word is part of the material or part of the description that follows. With that said, I would note that the OP asked for this in his first message (#39)...

"Is it possible to use a formula that starts at the first % mark (and then -2 or something to
include the number in front?) until the last % mark and then include the first word behind it?"

 

[István Hirsch]

Correct... if you have a multi-word material at the end, then as you said, there is no way to know if the second word is part of the material or part of the description that follows. With that said, I would note that the OP asked for this in his first message (#39)...

"Is it possible to use a formula that starts at the first % mark (and then -2 or something to
include the number in front?) until the last % mark and then include the first word behind it?"

Ok, the OP originally asked to include one word behind the last % character, but later came up with an example with a multi-word constituent and I thought if „Lambs wool” could appear in mid-position why couldn’t it (or an even longer constituent name) appear in end position? I suppose the description of compositions was not structured/checked before to avoid this.

 

[MrWouters]

Hi Rick & Istvan,

Thanks so much again for all your help. Indeed I requested it like that initially. I didn't notice fabric names made up of two words.
But as said earlier, this small change I'll have to do in the end is no trouble.

What you provided already solves the problem for the largest part. If I understand correctly though; If I would connect for instance lambs wool like lambs-wool (As Istvan mentioned also in one of his posts), it would solve everything right?

But then still, which formula would be better to use? Or doesn't it make a difference then?

Thanks!

 

[István Hirsch]

The formula in post #45 seems to handle correctly multi-word constituent names in any position if the two (or more) words are not joined with space, but some other character. It can be, „-„, for example:

Excel Workbook
	A	B
1	Y10000603 Woolen 70% Acrylic, 20% Lambs-wool, 10% Alpaca	70% Acrylic, 20% Lambs-wool, 10% Alpaca
2	Y10000603 Woolen 70% Acrylic, 20% Alpaca, 10% Lambs-wool	70% Acrylic, 20% Alpaca, 10% Lambs-wool
Sheet

If there are not many such constituents – before using the formula above – you can replace the space between them with another character by the use of Edit/Replace feature, e.g. you can replace each occurrence of „Lambs wool” with „Lambs-wool” in a single step. If you choose char(160) to join the words, the difference is not visible.

 

[MrWouters]

Ok great, that's the one I used last so I'll leave it as it is..

Thanks again!

 

[István Hirsch]

You are welcome.

 

[camle]

I did not know what to call this function, so I called it EpisodeName (thinking that is what you are retrieving), but you can rename it by changing the highlighted text...

Function [COLOR=#0000FF][B]EpisodeName[/B][/COLOR](DataLine As String) As String
  Dim X As Long, AfterEpisode As String, Parts() As String
  Parts = Split(DataLine)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*#x#*" Or Parts(X) Like "S#*E#*" Then
      Parts = Split(DataLine, " ", X + 2)
      AfterEpisode = Parts(UBound(Parts))
      If InStr(AfterEpisode, ".") Then AfterEpisode = Left(AfterEpisode, InStrRev(AfterEpisode, ".") - 1)
      [B][COLOR=#0000FF]EpisodeName[/COLOR][/B] = AfterEpisode
      Exit For
    End If
  Next
End Function

Can you help me with this vba code that you gave me?
I need it to get the series name.

ex.
Castle (2009) 6x10 The Good, the Bad & the Baby

I need the "Castle (2009)"

Thanks

 

[Rick Rothstein]

Can you help me with this vba code that you gave me?
I need it to get the series name.

ex.
Castle (2009) 6x10 The Good, the Bad & the Baby

I need the "Castle (2009)"

One example is usually not enough to decide if all the data will have the same structure, but let me take a guess. Assuming you always want all the text before (and including) the first closing parenthesis in the text, you do not need a UDF to do this, here is a direct formula that you can use...

=LEFT(A2,FIND(")",A2))

 

[camle]

In the vba code, it used "Parts like"
I would like to be able to add the different variation as I come about.

Thanks

 

[Rick Rothstein]

In the vba code, it used "Parts like"
I would like to be able to add the different variation as I come about.

I guess I am confused then. If you would be able to "add the different variation as I come about", then what is it about the code I posted in Message #57 that you are unable to do that with it now?