Unanswered: Displaying or extracting characters preceeding a comma
I should know this but...
What expression will produce all the characters in a string that precede a comma. Also, what expression will produce all of the characters that follow the comma. Example: If the string is "Smith, Bill", I need the expression that will produce "Smith" and the expression that will produce "Bill". Thanks for any help offered.
displaying or extracting characters up to or after a comma
Thanks "healdem". If I knew that all of the last names in the field were 5 characters long I could use Left([fieldname],5) or Mid([Fieldname],1,5) but the last names and first names are all of different lengths so I'm trying to figure out the proper syntax for "take all of the text up to the comma" and/or "take all of the text after the comma". Any additional help will be appreciated.
Try the function InStrRev to locate the comma and then you can parse the string into the two components:
strName is the lastname, firstname
intComma = InStrRev(strName, "," , Len(strName))
This should give you the length of the last name so you can then Left(strName, intComma) to get just the last name.
Then figure out the length of StrName and subtract intComma (probably plus 2 more to account for the comma and the space) and use the Right function to get the first name.
You have to be careful with how the data is entered here - since some names can contain commas before suffixes (Jr., III, M.D. etc.)
Hope this helps!
Checkout the Split function in VBA help. I've used this in VB code to split names into two strings. You can use "," as your delimeter and specify 2 splits (or use -1 to get all splits). As mentioned before, be cautious of names containing a ","