Extract Text between Ampersands? Cannot Use VBA.

[VBE313]

How can I extract text between ampersands? I want to have it up to 3 ampersands. I got the formula's to work up to only 1 ampersand. is there an easier way to get this without VBA? The data between the Ampersands should always start with "SO".

F1 Formula

=IFERROR(IF(LEFT(A1,2)="SO",LEFT(A1,(FIND("&",A1)-2)),""),A1)

G1 Formula

=IFERROR(IF(LEFT(IF(RIGHT(TRIM(RIGHT(A1,(FIND("&",A1,1)-1))),1)<>"S",TRIM(RIGHT(A1,(FIND("&",A1,1)*1))),TRIM(RIGHT(A1,(FIND("&",A1,1)-1)))),1)="&",TRIM(RIGHT(A1,(FIND("&",A1,1)-2))),IF(RIGHT(TRIM(RIGHT(A1,(FIND("&",A1,1)-1))),1)<>"S",TRIM(RIGHT(A1,(FIND("&",A1,1)*1))),TRIM(RIGHT(A1,(FIND("&",A1,1)-1))))),"")

 

[AhoyNC]

Why not use Text to Columns under the Data ribbon, using & as the delimiter.

 

[Peter_SSs]

Try this, copied across and down.

20 10 01.xlsm
	A	B	C	D	E
1	SO123 & SO456	SO123	SO456
2
3	SO1 & SO2 & SO3 & SO4	SO1	SO2	SO3	SO4
Extract

Cell Formulas
Range	Formula
B1:E3	B1	=TRIM(MID(SUBSTITUTE("&"&$A1,"&",REPT(" ",100)),COLUMNS($B:B)*100,100))

 

[Trebor76]

If the "SO" codes are always 13 characters long separated by " & " then you could do this (assumes the text is in cell A4):

Cell B4: =LEFT(A4,13)
Cell C4: =MID(A4,16,13)
Cell D4: =MID(A4,31,13)
Cell E4: =MID(A4,46,13)

This is up to four codes. If there are less a blank will be returned.

HTH

Robert

 

[VBE313]

Why not use Text to Columns under the Data ribbon, using & as the delimiter.

I am using Office Scripts to automate a daily task. I am not sure if Text to Columns is available in TypeScript as of right now.