Rearrange text in cell

[Switch]

Hi all,

Can anyone tell me how to write a formula that will change the original text in Col A of this example to read as the text in columnB on the example.

Book2
	A	B	C	D
1	OriginalTextinthiscolumn	Formulamustreturnthis
2	AntioxidantPlus(60)_03630	03630_AntioxidantPlus(60)
3	AvenaComplex(42)_03346	03346_AvenaComplex(42)
4	BioC-Complex1000(60)_03624	03624_BioC-Complex1000(60)
5	Bio-CComplex125g_03606	03606_Bio-CComplex125g
6	Bio-ChromiumPlus(60)_03633	03633_Bio-ChromiumPlus(60)
7	Bio-Disc(60)_03634	03634_Bio-Disc(60)
8
9
Sheet1

Thank you
Switch

 

[Haluk]

Hi;

i'm sure that, one of the professionals will post a shorter formula but, this is my suggestion; (you may need to change " ; " with " , " )

=MID(A1;FIND("_";A1;1)+1;FIND("_";A1;1))&"_"&MID(A1;1;FIND("_";A1)-1)

 

[Aladin Akyurek]

In B2 enter & copy down:

=WMID(A2,2,1,"_")&"_"&WMID(A2,1,1,"_")

WMID part of the morefunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html

Otherwise, try...

=RIGHT(A2,LEN(A1)-SEARCH("_",A2)-1)&"_"&LEFT(A2,SEARCH("_",A2)-1)