Extract Text Data

[tmcrouse]

I can't find how to extract the last name from a field that has last name, first name. I want to extract everything prior to the comma and the last name are varying lengths so I can't use =left([saename],5)

The field looks like this:



Jones, Sara
Crouse, John
Millichecki, Tracy

[pbaldy]

You can use the Left() function in combination with the InStr() function to find the position of the comma.

[tmcrouse]

I tried that but since the position of the commas change because of the length of the last name it chops it up. I am trying to get a separate field for the last name and separate for first name without manually doing this for 9007 rows of data. You can take two fields and add them together and put a comma between them like Name:[lastname] & "," & [firstname] but there has to be a way to undo something like that. I have to compare this 9007 table to another table that has 9858 rows and the keys are 1) SAE last name and 2) SAE first name. I guess since I can't find anyone that knows the answer to this my only option is to manually do it. Which will take days. But no one on any forum seems to know.

[ConneXionLost]

Give this a try:

LastName: Left([saename],InStr([saename],",")-1)

Cheers,

[tmcrouse]

It just returns #error

[ConneXionLost]

How are you "extracting" this data? By query? If so, can you post the SQL?