What formula can I use to extract variable length strings, where all I know is the beginning and end points of the string. So for example, I have a large list of transactions where I need to extract the text between the "*", which are different lengths and sometimes different characters. (examples below)
REMARK=TRN*1*3107979027*000000000
REMARK=TRN*1*7037/09-10-19*0000000
REMARK=TRN*1*001270017788519*0000000
Thanks!
Text to Column is a good choice, but if you want to use formula to get the result, try these formulas:
B1: =LEFT(A1,FIND("*",A1)-1)
C1: =MID(A1,FIND("*",A1)+1,FIND("*",A1,FIND("*",A1)+1)-FIND("*",A1)-1)
D1: =MID(A1,FIND("*",A1,FIND("*",A1)+1)+1,FIND("*",A1,FIND("*",A1,FIND("*",A1)+1)+1)-FIND("*",A1,FIND("*",A1)+1)-1)
E1: =RIGHT(A1,LEN(A1)-FIND("*",A1,FIND("*",A1,FIND("*",A1)+1)+1))
Considering the sample data, to extract text between * sign, you need to do the following:
How it works:
L54 & M54.Formula in Cell L55, fill it across.
=SUBSTITUTE(MID(SUBSTITUTE("*" & $J55&REPT(" ",6),"*",REPT(",",255)),L$54*255,255),",","")
L$54*255, formula reads it like 2*255 then 3*255, where 2 & 3 are the text position between *
sign.Adjust cell references in the formula as needed.
Assuming each string is similar to what you show, with 3 substrings that are between asterisks *, try in some cell:
=INDEX(TRIM(MID(SUBSTITUTE($A1,"*",REPT(" ",99)),{99,198,297},99)),COLUMNS($A:A))
And fill right two more columns. Then select the three columns and fill down as far as necessary.
Source Data
Results
