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:

[TABLE="width: 604"]
<tbody>[TR]
[TD="width: 604"]897/C///Amarnath Leena CD20110316 09105348[/TD]
[/TR]
[TR]
[TD="width: 604"]I need to pull everything after the "///"

Another example would be:
[TABLE="width: 604"]
<tbody>[TR]
[TD="width: 604"]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?[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="width: 604"][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi
Welcome to the board

Try:

=MID(A1,find("///",A1)+3,LEN(A1))

 

[Domski]

Try:

=TRIM(RIGHT(SUBSTITUTE(A1,"///",REPT(" ",LEN(A1))),LEN(A1)))

Dom

 

[WmK]

Terrific!! IT WORKS
Can you tell me what the "+3" does?

 

[Rick Rothstein]

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.

 

[Trouttrap2]

Another way. Better late than never.

=REPLACE(C4,1,FIND("///",C4)+2,"")

 

[WmK]

Got it, Thanks

 

[camle]

Got it, Thanks

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

<colgroup><col width="314"></colgroup><tbody>
[TD="width: 314"]American Eats 1x01 Soda
[/TD]

</tbody>

<tbody>
[TD="width: 314"]American Eats 1x12 Cereal History in a Bowl

<colgroup><col width="314"></colgroup><tbody>
[TD="width: 314"]American Pickers 2010x04 Invisible Pump
[/TD]

</tbody>

[/TD]

</tbody>

<colgroup><col width="314"></colgroup><tbody>
[TD="width: 314"]Diners, Drive-Ins and Dives S01E08 Homestyle
[/TD]

</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

 

[Rick Rothstein]

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))

 

[camle]

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