Extract text after specific char in a string

WmK

New Member
Joined
Jul 17, 2012
Messages
5
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>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Terrific!! IT WORKS
Can you tell me what the "+3" does?

FIND will return the character position for the first character of the text string you searched for. Since that text string is 3 characters long, you have to add 3 so that the MID function looks at the character after the last character of that text string in order to be able to grab the characters you wanted.
 
Upvote 0
Got it, Thanks

I have something similar if you don't mind answering for me.
Tv shows: Name, Season, Episode, Title

American Eats 1x01 Soda

<colgroup><col width="314"></colgroup><tbody>
</tbody>
American Eats 1x12 Cereal History in a Bowl
American Pickers 2010x04 Invisible Pump

<colgroup><col width="314"></colgroup><tbody>
</tbody>

<tbody>
</tbody>
Diners, Drive-Ins and Dives S01E08 Homestyle

<colgroup><col width="314"></colgroup><tbody>
</tbody>

Would like to pull out the:
1x01
1x12
2010x14
S01E08

The Episode can be up to 3 digits.

I get info from TVDB.com and get info from the media center hard drives to compare what files I need to record in excel.

Thanks,
Cam
 
Upvote 0
I have something similar if you don't mind answering for me.
Tv shows: Name, Season, Episode, Title

American Eats 1x01 Soda

<tbody>
</tbody>
American Eats 1x12 Cereal History in a Bowl
American Pickers 2010x04 Invisible Pump

<tbody>
</tbody>

<tbody>
</tbody>
Diners, Drive-Ins and Dives S01E08 Homestyle

<tbody>
</tbody>

Would like to pull out the:
1x01
1x12
2010x14
S01E08

The Episode can be up to 3 digits.

I get info from TVDB.com and get info from the media center hard drives to compare what files I need to record in excel.
I don't think there can be a foolproof solution unless you can tell us about how that "Season-Episode" number is formed. For example, will it always have an x or E with a number on each side of it like your examples show or could it have other constructions? Below is a formula that will work AS LONG AS there are no numbers in the shows Name...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))-1)," ",REPT(" ",999)),999))
 
Upvote 0
I don't think there can be a foolproof solution unless you can tell us about how that "Season-Episode" number is formed. For example, will it always have an x or E with a number on each side of it like your examples show or could it have other constructions? Below is a formula that will work AS LONG AS there are no numbers in the shows Name...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))-1)," ",REPT(" ",999)),999))

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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top