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]

Also see a little problem:

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

pulls out (2009) rather then 6x10

I should have included this one in the examples... Sorry

Cam

 

[camle]

Yes it will always have either the "x" or the S##E###

Format:
##x##
##x###
S##E##
S##E##E##
S##E###E###

You know your Formulas!!!!


Thanks,
Cam

I see something I should have included in my examples:

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

It pulls out : (2009) rather than 6x10

Thanks,
Cam

 

[Rick Rothstein]

I see something I should have included in my examples:

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

It pulls out : (2009) rather than 6x10

As I said it would... " Below is a formula that will work AS LONG AS there are no numbers in the shows Name..." I did not develop my formula around the "x" and "E" idea because I did not know that was the complete set of patterns at the time... now that I know it (from your previous posting), I am looking into a formula to work with those patterns. I'll be back as soon as I have one.

 

[Rick Rothstein]

I am looking into a formula to work with those patterns. I'll be back as soon as I have one.

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.

 

[camle]

You have been a big help!
I think that will do just fine

Thanks,
Cam

 

[camle]

You have been a big help!
I think that will do just fine

Thanks,
Cam

American Pickers 2010
American Pickers 2010
American Pickers 2011
American Pickers 2012
American Pickers 2012
American Pickers 2012

a formula to return:

American Pickers
2010=2
2011=1
2013=3

I was trying =COUNTIF(A4,VLOOKUP($A$1,GetFiles!D1:F5000,3))
where:
A4=season#
A1=American Pickers

But no luck

Thanks,
Cam

 

[Rick Rothstein]

American Pickers 2010
American Pickers 2010
American Pickers 2011
American Pickers 2012
American Pickers 2012
American Pickers 2012

a formula to return:

American Pickers
2010=2
2011=1
2013=3

I was trying =COUNTIF(A4,VLOOKUP($A$1,GetFiles!D1:F5000,3))
where:
A4=season#
A1=American Pickers

Is this a separate question than the one you asked when you started this thread or is the solution to what you have now posted supposed to be integrated with your original question in some way? In order answer your current question, though, we need to know something about the layout of the data (both original data and how you wanted arranged afterwards), specifically regarding other data (that is, shows different from American Pickers) that may be on the sheets.

 

[camle]

Is this a separate question than the one you asked when you started this thread or is the solution to what you have now posted supposed to be integrated with your original question in some way? In order answer your current question, though, we need to know something about the layout of the data (both original data and how you wanted arranged afterwards), specifically regarding other data (that is, shows different from American Pickers) that may be on the sheets.

Well, I guess its a separate question.
Do I need to start another thread?

 

[Rick Rothstein]

Well, I guess its a separate question.
Do I need to start another thread?

I think that would be a good idea... that way you will get more people involved who might be able to help (people who decided not to follow this thread for whatever reasons won't think to look here for a new question by you). Don't forget to give a clear picture of your current data layout (especially if there is more than one show per worksheet... don't just show one and hope we can figure out there is more and also figure out how it is arranged) and how the resulting data should look. Also tell us if you want the original data modified or if you want the modified data placed elsewhere (tell us where if that applies). Remember, when you ask a question on a forum, you are talking to people who have absolutely no idea what you have nor what you want done with it... you have to tell us everything (no matter how obvious it looks to you).

 

[camle]

Would you please look at the link below:

http://www.mrexcel.com/forum/excel-questions/782461-vlookup-match-count.html#post3832016