I've got a CSV file with contacts inside that I want to import in an online contacts list.
Unfortunately, the CSV file has one single column for the full name, and does not differentiate "First Name" and "Last name" columns, which I need. I was wondering if there was an Excel function to make the split (or any other suggestion, taking into account that I'm a C# developer but I don't know Excel VB scripting).
The good news is that the "Full name" column does acctually always start with the Last name in upper case, followed by the first name in camel case, with the last name containing possibly an apostrophe and both first and last name can contain multiple words. Some examples follow:
1) LASTNAME Firsname; Other data; ...
2) LASTNAME LASTNAME Firsname; Other data; ...
3) LASTNAME' LASTNAME Firsname; Other data; ...
4) LASTNAME' LASTNAME LASTNAME Firsname Firstname; Other data; ... Which I need to transform into:
1) Lastname; Firsname; Other data; ...
2) Lastname Lastname; Firsname; Other data; ...
3) Lastname' Lastname; Firsname; Other data; ...
4) Lastname' Lastname Lastname; Firsname Firstname; Other data; ...Thankyou for your help.
21 Answer
I'll assume you have imported the CSV file and the list of names is in column A.
This formula creates a "helper" column containing the position of the last letter of the first name:
=MATCH(1,(CODE(MID(A2,ROW(A$1:A$50),1))>96)*(CODE(MID(A2,ROW(A$1:A$50),1))<123),0)-3
It is an array formula, and must be entered with CTRLShiftEnter rather than just Enter. If entered correctly, the formula will be surrounded by curly brackets in the formula bar. This formula has been filled down from B2 in the table below.
How it works: MID() creates an array of all the letters in the name (up to 50 letters). ROW() is used to generate a list of numbers from 1 to 50. So, if any of your names could be longer than 50 characters, then you should increase the term A$50 as necessary.
CODE() converts the array of letters into their ASCII codes. The comparisons >96 and <123 check for lower case ASCII letters, while converting the arrays to True/False values with True for any lower case letter.
The multiplication performs the equivalent of a logical AND(), and converts the True/False values into 1's and 0's. Finally, MATCH() finds the position of the first 1 in that array, which is the position of the first lower case letter in the name. Subtracting 3 identifies the last letter of the first name.
Now, using the helper column, the last names can be listed in column C with:
=PROPER(LEFT(A2,B2))
This takes the all-caps portion of column A and converts it in into what Excel calls "proper" case, which seems to be the same as "camel" case.
The first names are listed in column D with:
=RIGHT(A2,LEN(A2)-B2-1)
