Extraction of First and Last Name from a String Containing Values Separated by Commas
In this example we’re starting from the first column which shows us the last name, the title in the middle and the first name separated by commas,
here we want to extract the first and the last name from a string containing these values separated by commas, with the title in the middle, which we want to remove.
So, we need to reach out to the second column as we see, but how we could do it?
We start calculating the position of the two commas and then we use these numbers to extract the right part of the text,
to determine the positions of the commas we used the FIND() function, which returns the starting position of one text string within another text string, syntax;
We used the following codes to determine the position of the commas;
These codes mean that;
- For the first comma, find it in the column “Name” from the table “Table”, and if it’s not founded, then put BLANK by using the IFERROR()
- For the second one, search in the “Name” column, but start looking for it after one digit from the position of the 1st comma,
Now after determining the positions of the commas, we can start solving the scenario as follows;
What we want as a final result, showing us words without extra spaces between them, just one space by a word, to do this, we’ll use the TRIM() function, which removes all spaces from text except for single spaces between words, syntax TRIM (<text>),
As we can see from the first column, the first name is stated at the end of the “Name” column, and as we want to put the FirstName at the beginning of the new column, we want to extract a part from the “Name” column which is in somewhere in the column, to do so, we’ll use a function that lets us take any part from a text, defining the text from where it must start, and the number of characters that we want to retrieve, this one is MID(),
MID() Returns a string of characters from the middle of a text string, given a starting position and length. Syntax:
MID (<text>, <start_num>, <num_chars>)
In the MID() function, we’ll say that, if the second comma is existed, then take the existed text after one digit from it, if not, take the ones that after one digit from the first comma, and to define the length of the characters that we want to retrieve after commas, we’re using the LEN() function,
At this step, we just extracted the first name from the “Name” column, now we need to extract the second name and add it to it,
We’re saying that, if the first comma is existed by codding IF(ISNUMBER()), then put “one space” and concatenate it with the last name, extracting it by using the LEFT() function that will start from the left side of the column where the last name is existed and go forward until one digit before the first comma, or if the first comma isn’t existed then put a BLANK instead of it,
And the result as we can see here from the second column, we have the full name without the title and extra spaces,
Hope this helps!
KENAN JADDENE