HELP: spliting a string

[gtben]

Hi all,

Can anyone tell me the formula to split the a6439d01m,a2638dm,a8399em....from the string

I have this kind of strings:

http://www.greenturtle.com/images/op..._a6439d01m.jpg
http://www.greenturtle.com/images/op...ts_a2638dm.jpg
http://www.greenturtle.com/images/op...ts_a8399em.jpg
......
......

I need to split this a6439d01m from the string

Thanks

[Pete_UK]

You can't see the full string on the forum, but it appears that the part you want is preceded by an underscore, and then followed by .jpg, so if the first string is in A1 and there is only one underscore you can try this:

=MID(A1,FIND("_",A1)+1,LEN(A1)-FIND("_",A1)-4)

then copy this down.

Hope this helps.

Pete

[gtben]

Hi pete,

Thanks for your help but the result of you formula is return this answer: black_men_tshirts_a11001a01m

.../images/options/*******-t-shirt_black_men_tshirts_a11001a01m.jpg

and I need just: a11001a01m

[Rambo4711]

If you want to split into two cells then use Text to columns from the Data menu. Chose delimeter as Other and type an underscore in the box.

Then you do the same with the new B column and chose dot as delimeter.

[gtben]

I don't want to split into two cells, i just need the number with the letters, like a11001a01m

let say that the string is in A1 and i need to cut just "a11001a01m" from the string
i don't need this "_black_men_tshirts"

[Pete_UK]

You can try this:

=MID(A1,FIND("s_",A1)+2,LEN(A1)-FIND("s_",A1)-5)

Might not work for all strings.

Hope this helps.

Pete

[Rambo4711]

I don't want to split into two cells, i just need the number with the letters, like a11001a01m

let say that the string is in A1 and i need to cut just "a11001a01m" from the string

If you use Text to column as my suggestion you will have the first part in column A and the cut information you wanted in column B. The remaining .jpg will be in column C. But if you want the full string to be intact, you cannot use Text to columns.

[AlKey]

Formula in B1 and copy it down

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",255)),255)),LEN(TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",255)),255)))-4)

	A	B
1	http://www.greenturtle.com/images/op..._a6439d01m.jpg	a6439d01m
2	http://www.greenturtle.com/images/op...ts_a2638dm.jpg	a2638dm
3	http://www.greenturtle.com/images/op...ts_a8399em.jpg	a8399em
4

[gtben]

Hi AlKey,

Thanks a lot!!! it works like a magic!
You are the best!

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).