Solved
Convert excel formula into Access formula
Posted on 2008-06-16
I'm trying to run a formula in access that I have been using for excell. I'm new to the world of Access so I don't know how to do name extraction in Access. I have the following formulas in excell that I used to extract first middle last suffix from a single cell that I now want to use in access to run a query.
MIDDLE INITIAL:
=IF(ISERROR(TRIM(MID(A2,FIND(" ",A2,FIND(",",A2)+2),FIND(" ",A2,FIND(" ",A2,FIND(",",A2)+2)+1)-FIND(" ",A2,FIND(",",A2)+2)))),IF(ISERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(",",A2)+2))),"",RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(",",A2)+2))),TRIM(MID(A2,FIND(" ",A2,FIND(",",A2)+2),FIND(" ",A2,FIND(" ",A2,FIND(",",A2)+2)+1)-FIND(" ",A2,FIND(",",A2)+2))))
SUFFIX:
=IF(ISERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,FIND(",",A2)+2)+1))),"",RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,FIND(",",A2)+2)+1)))
LAST NAME:
=if(a2="","",LEFT(A2,(SEARCH(",",A2))-1))
FIRST NAME:
=TRIM(MID(A2,FIND(",",A2)+1,(FIND(" ",A2,FIND(",",A2)+2)-FIND(",",A2))))