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>

 

[camle]

While I am sure there must be one, I could not think of an efficient way to do what you want with a formula (although I am sure some of our formula wizards out there will come up with one). However, I was able to develop a UDF (user defined function) that will do what you want...

Function Episode(DataLine As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(DataLine)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*#x#*" Or Parts(X) Like "S#*E#*" Then
      Episode = Parts(X)
      Exit For
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Episode just like it was a built-in Excel function. For example,

=Episode(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Needing a little more help.

Formula format: (reminder)
Format:
##x##
##x###
S##E##
S##E##E##
S##E###E###

Can you say find the "x" with #'s on both sides and then go to the next space capture the text after that minus the file extension.
Ex.
American Pickers 2010x04 Invisible Pump.ts

I need the "Invisible Pump"

Where is the best place to learn Excel Vba other than here (begging for answers)


Thanks

 

[Rick Rothstein]

Needing a little more help.

Formula format: (reminder)
Format:
##x##
##x###
S##E##
S##E##E##
S##E###E###

Can you say find the "x" with #'s on both sides and then go to the next space capture the text after that minus the file extension.
Ex.
American Pickers 2010x04 Invisible Pump.ts

I need the "Invisible Pump"

You want this only for text that has #x# in it, not for the S##E## text? I ask because it is easier to write code when all the requirements are known rather than write it only for one condition only to have to modify it for a second condition later on.

 

[camle]

You want this only for text that has #x# in it, not for the S##E## text? I ask because it is easier to write code when all the requirements are known rather than write it only for one condition only to have to modify it for a second condition later on.

Oh yes, both formats please.

 

[camle]

Also have another post if you would care to look at it.

Thanks

http://www.mrexcel.com/forum/excel-...applications-file-management.html#post3838480

 

[camle]

you're going to have to send me a bill

 

[Rick Rothstein]

Oh yes, both formats please.

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

 

[camle]

Amazing!!

Thank You

 

[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

Say if I wanted to add another filter for:
- ####-##-## -

I would change:
From:
If Parts(X) Like "*#x#*" Or Parts(X) Like "S#*E#*" Then


To:
If Parts(X) Like "*#x#*" Or Parts(X) Like "S#*E#*" Or Parts(X) Like "- ####-##-## -" Then

and so on?


</pre>

 

[Rick Rothstein]

Say if I wanted to add another filter for:
- ####-##-## -

I would change:
From:
If Parts(X) Like "*#x#*" Or Parts(X) Like "S#*E#*" Then


To:
If Parts(X) Like "*#x#*" Or Parts(X) Like "S#*E#*" Or Parts(X) Like "- ####-##-## -" Then

and so on?

Are these really related? I got the impression from you other post (http://www.mrexcel.com/forum/excel-...applications-file-management.html#post3838480) that this new pattern was for a different purpose. To answer the question you asked, though, no, you cannot just do that because the parts array was formed by splitting using the space character as a delimiter and your suggested pattern has a space inside it, so the Split function would separate the free-floating dashes on the end from the text between them into different array elements, hence a different kind of test would have to be designed. This what I meant when I said earlier about knowing all the conditions beforehand... it is much easier to design a solution knowing all the conditions upfront as opposed to designing a solution only to have to pretty much throw it away and start over when a new condition is revealed. So I'll ask again... is this new pattern really another condition like the original ones or is it separate question as the above link would suggest?

 

[camle]

It is related to the other post (mainly).
But from the lessons that I have received from you, I was trying to put together code myself.
Trying to learn what does what? I appreciate your help very much.

Thanks