HELP: on a string

gtben

New Member
Joined
Jan 15, 2014
Messages
6
Hi all,

how do i cut the last part from the url without "T-Shirt-#"

for example:

url/global/Holidays/Halloween/Frightening-Pumpkin-T-Shirt-1117/ -------------return Frightening-Pumpkin
url/Holidays/Christmas/Who-Needs-Santa-Ive-Got-Grandma-T-Shirt-7391/ --- return Who-Needs-Santa-Ive-Got-Grandma
url/Couples/Hes-My-King-T-Shirt-7375/ -------------------------------------return Hes-My-King

Thanks
for your kind help!
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
Hi all,

how do i cut the last part from the url without "T-Shirt-#"

for example:

url/global/Holidays/Halloween/Frightening-Pumpkin-T-Shirt-1117/ -------------return Frightening-Pumpkin
url/Holidays/Christmas/Who-Needs-Santa-Ive-Got-Grandma-T-Shirt-7391/ --- return Who-Needs-Santa-Ive-Got-Grandma
url/Couples/Hes-My-King-T-Shirt-7375/ -------------------------------------return Hes-My-King

Thanks
for your kind help!
For the sample data you posted, this appears to work...

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE("-T-Shirt-"&A1,"-T-Shirt-",REPT(" ",299)),598),299)),"/",REPT(" ",299)),299))
 

gtben

New Member
Joined
Jan 15, 2014
Messages
6
For the sample data you posted, this appears to work...

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE("-T-Shirt-"&A1,"-T-Shirt-",REPT(" ",299)),598),299)),"/",REPT(" ",299)),299))

Great!!! you are BEST!!
Thanks a lot!! :)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
Great!!! you are BEST!!
Thanks a lot!! :)
Assuming the text "-T-Shirt-" will only ever appear once in a given URL, here is a simpler formula that should also work for you...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("-T-Shirt-",A1)-1),"/",REPT(" ",299)),299))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,624
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top