Extract Last part of the string

[karthikgmkk]

Hi,

I have values like below, where I have to extract the last part of the string in every value.

It would be great if you also can provide the formula for extracting 2 part of the string as well.

[TABLE="width: 314"]
<colgroup><col></colgroup><tbody>[TR]
[TD]topnav-_-whatsnew-_-topbar[/TD]
[/TR]
[TR]
[TD]topnav-_-whatsnew-_-thisweek[/TD]
[/TR]
[TR]
[TD]topnav-_-designers-_-designera-z[/TD]
[/TR]
[TR]
[TD]topnav-_-clothing-_-dresses[/TD]
[/TR]
[TR]
[TD]topnav-_-sale-_-topbar[/TD]
[/TR]
[TR]
[TD]topnav-_-sale-_-clothing[/TD]
[/TR]
</tbody>[/TABLE]

Can you please help me with the formula?

Regards,
Karthik

 

[Jan Mach]

If the separator is ALWAYS "-_-", write

Dim arr$()
arr = Split(s, [COLOR=#222222][FONT=Verdana]"-_-"[/FONT][/COLOR])

where s is your string. The arr array contains adequate parts of s.

 

[karthikgmkk]

Instead Macro, can we achieve by writing formula?

 

[jtakw]

Hi,

Is this what you want?

Book1
	A	B	C	D
1			2 parts	last part
2	topnav-_-whatsnew-_-topbar		whatsnew-_-topbar	topbar
3	topnav-_-whatsnew-_-thisweek		whatsnew-_-thisweek	thisweek
4	topnav-_-designers-_-designera-z		designers-_-designera-z	designera-z
5	topnav-_-clothing-_-dresses		clothing-_-dresses	dresses
6	topnav-_-sale-_-topbar		sale-_-topbar	topbar
7	topnav-_-sale-_-clothing		sale-_-clothing	clothing
Sheet7

Cell Formulas
Range	Formula
C2	=MID(A2,FIND("-_-",A2)+3,255)
D2	=MID(A2,FIND("^",SUBSTITUTE(A2,"-_-","^",2))+3,255)

Formulas copied down.

 

[karthikgmkk]

Perfect !! It is working

Thanks a lot

 

[jtakw]

You're very welcome.

 

[Rick Rothstein]

Perfect !! It is working

You can also do this easily enough manually (no formulas required)...

1) Select the entire column with your data in it, press CTRL+H to bring up the replace dialog box, type "-_-" in the "Find what" field (without the quote marks), type "_" in the "Replace with" field (again, without the quote marks), click the "Options>>" button and make sure there are no check marks in the checkboxes, then click the "Replace All" button.

2) The column will still be highlighted so bring up the "Text To Columns" dialog box (Data tab, Data Tools panel), select "Delimited" and press the "Next" button, uncheck the checkboxes and then check the "Other" checkbox and put an underscore character in the blank field next to it, click the "Finish" button.