Excel MID function

microsoft excel

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!

Best Answer

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) enter image description here

C1: =MID(A1,FIND("*",A1)+1,FIND("*",A1,FIND("*",A1)+1)-FIND("*",A1)-1) enter image description here

D1: =MID(A1,FIND("*",A1,FIND("*",A1)+1)+1,FIND("*",A1,FIND("*",A1,FIND("*",A1)+1)+1)-FIND("*",A1,FIND("*",A1)+1)-1) enter image description here

E1: =RIGHT(A1,LEN(A1)-FIND("*",A1,FIND("*",A1,FIND("*",A1)+1)+1)) enter image description here

Related Question