Excel Parsing Formulae

Parse rightmost word in a cell:
=RIGHT(A1, LEN(A1)-FIND("*",SUBSTITUTE(A1," ", "*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

Parse location:
=MID(A2,FIND("Location: ",A2,1)+14,FIND("(",A2,FIND("Location: ",A2,1))-FIND("Location: ",A2,1)-14)
=MID(A2,FIND("Location: ",A2,1)+14,FIND("(",A2,FIND("Location: ",A2,1))-FIND("Location: ",A2,1)-14)
[ Number of characters: FIND("(",A2,FIND("Location: ",A2,1))-FIND("Location: ",A2,1)-15 ]

ParseName(fwd):
=PROPER(MID(A2,SEARCH("Name: ",A2,1)+6,SEARCH("Email:",A2,1)-(SEARCH("Name:",A2,1)+9)))
=PROPER(MID(A2,SEARCH("Name: ",A2,1)+6,SEARCH("Phone:",A2,1)-(SEARCH("Name:",A2,1)+8)))

ParseEmail:
=TRIM(LOWER(MID(A2,FIND("mailto:",A2,1)+7,(FIND("Home Location: ",A2,1))-(FIND("mailto:",A2,1)+9))))
=TRIM(LOWER(MID(A2,FIND("Email: ",A2,1)+8,(FIND("Phone: ",A2,1))-(FIND("Email: ",A2,1)+12))))

Concatenate middle and last names (& appellations):
=PROPER(TRIM(CONCATENATE(IF(LEN(C2)=1,C2&".",C2),IF(ISBLANK(D2),""," "&D2),IF(ISBLANK(E2),""," "&E2),)))
=PROPER(TRIM(CONCATENATE(IF(LEN(C2)=1,C2&".",C2),IF(ISBLANK(D2),""," "&D2),IF(ISBLANK(E2),""," "&E2),IF(ISBLANK(F2),""," "&F2))))
=PROPER(TRIM(CONCATENATE(IF(LEN(D2)=1,D2&".",D2),IF(ISBLANK(E2),""," "&E2),IF(ISBLANK(F2),""," "&F2))))
=PROPER(TRIM(CONCATENATE(IF(LEN(E2)=1,E2&".",E2),IF(ISBLANK(F2),""," "&F2),IF(ISBLANK(G2),""," "&G2))))
=PROPER(TRIM(CONCATENATE(IF(LEN(F2)=1,F2&".",F2),IF(ISBLANK(G2),""," "&G2),IF(ISBLANK(H2),""," "&H2))))
=PROPER(TRIM(CONCATENATE(IF(LEN(F2)=1,F2&".",F2),IF(ISBLANK(G2),""," "&G2),IF(ISBLANK(H2),""," "&H2),IF(ISBLANK(I2),""," "&I2))))
=PROPER(TRIM(CONCATENATE(IF(LEN(G2)=1,G2&".",G2),IF(ISBLANK(H2),""," "&H2),IF(ISBLANK(I2),""," "&I2))))
=PROPER(TRIM(CONCATENATE(IF(LEN(G2)=1,G2&".",G2),IF(ISBLANK(H2),""," "&H2),IF(ISBLANK(I2),""," "&I2),IF(ISBLANK(J2),""," "&J2))))

Parse location (in Body):
=MID(A2,FIND("Location: ",A2,1)+13,FIND("(",A2,FIND("Location: ",A2,1))-FIND("Location: ",A2,1)-14)

Parse location (in Subject):
=IF(SEARCH(" in ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" in ",A2,1)-3),)
=IF(SEARCH(" in ",C2,1),RIGHT(C2,LEN(C2)-SEARCH(" in ",C2,1)-3),)
=IF(SEARCH(" in ",D2,1),RIGHT(D2,LEN(D2)-SEARCH(" in ",D2,1)-3),)
=IF(SEARCH(" in ",C2,1),RIGHT(C2,LEN(C2)-SEARCH(" in ",C2,1)-3),IF(SEARCH(" for ",C2,1),RIGHT(C2,LEN(C2)-SEARCH(" for ",C2,1)-4),))
=IF(SEARCH(" in ",D2,1),RIGHT(D2,LEN(D2)-SEARCH(" in ",D2,1)-3),IF(SEARCH(" for ",D2,1),RIGHT(D2,LEN(D2)-SEARCH(" for ",D2,1)-4),))
-------------------------------------------------
Post a Comment