Use Excel’s MID and FIND to extract characters from varying positions

Use this to grab the values after a certain value, of the whole string the value doesn’t exist. The concatenation ensure that the value is always found. The final Len(A1) forces the entire length of the string after the value to be shown regardless of what it it. the final A1 in the formula shows the entire string if the value is not found anywhere in the string.

IF(FIND(“.”,CONCATENATE(A1,”.”)) <= LEN(A1), MID(A1, FIND(“.”, A1) + 1,LEN(A1)), A1)

hhhh.yuuuu yuuuu
hhgff.1.2.3 1.2.3
fgdfgf.fdgdfg.fgfd fdgdfg.fgfd
t.6364y 6364y
A888999 A888999

Use this to find values between two values.

555 (999-0000) 999-0000

This one find text always to the right of a value (assumes within the last 5 chars) of a string.

IF(FIND(“.”,CONCATENATE(A13,”.”)) <= LEN(A13),RIGHT(A13,FIND(“.”,RIGHT(A13,5))+2),A13)
filename.doc .doc
hfhg/fghfgh/hh hfhg/fghfgh/hh

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.