How do I remove an extra space while performing the split function?

Question

I need to use the vlookup function and the space in front of the first name is being an issue

I tried the data clean up to trim white spaces but it didn't work. I used the split function to split the name value (last name, first name) into separate values. After that I used the concantenate function to merge it into first name (space) last name, however, the space in front of the split value remains!

Answer 1

You can use the TRIM function to remove any extra spaces from these values before concatenating them again. Example: =TRIM(A2) & " " & TRIM(B2).

You should also remove any leading or trailing spaces from your lookup value by incorporating the TRIM function into your VLOOKUP: =VLOOKUP(TRIM(X2), A2:B100, 2, FALSE).

Note: If you want to trim an entire column, you can use: =ARRAYFORMULA(TRIM(A:A)) in a blank cell outside the range.

Answer 2

If your names are in the form Last, First and you want them in the form First Last, you can use:

=JOIN(" ",CHOOSECOLS(SPLIT(A1,", ",FALSE),2,1))

You could also use REGEXREPLACE:

=REGEXREPLACE(A1,"(.*), (.*)","$2 $1")