I’m a noob, so bare with me please.
I’m importing a customer list. The customer list has only one field for NAME.
I’ve imported using the SPACE and COMMA delimiters.
This results in 3 columns because some of the names have a middle initial.
I would like to be able to import the names into two columns, First Name (and middle initial) and Last Name so I can sort the list by last name.
Suggestions?
Thanks!
Chaz
Nobody is able to help with this since you do not give us the faintest clue about the EXACT format of your import data.
It has a comma and a space? Where EXACTLY?
Kennedy, John Fitzgerald
John Fitzgerald, Kennedy
Kennedy, John, Fitzgerald
John, Fitzgerald, Kennedy
Where is the optional middle name and which delimiters are used between what? You try to command a typical computer program from the 90ies that does not have any artificial intelligence. You have to be precise.
Sorry about that.
Let’s try again…
There is only a “Name” field in the customer entry form, which is usually entered as Joe Kennedy. However, many of the customer names have middle initials, ex. Joe H. Kennedy.
If I use a space delimiter for the import, for Joe Kennedy, Calc will put Joe in column 1 and Kennedy in column 2 , and for Joe H. Kennedy, calc will put Joe in column 1, H. in column 2 and Kennedy in column 3.
My purpose is to have only 2 columns; Joe Kennedy - first name in column 1 and last name in column 2; and Joe H. Kennedy - first name and middle name (or initial) in column 1 with only the last name in column 2.
Is that more precise?
(I should not have used the comma in the import.)
Post a row (change customer name for privacy) of your source CSV. Is it quoting things like “Kennedy, Joe H.” or is it always just “Joe H. Kennedy” or “Joe Kennedy”? That is, are there ever commas in the name field?
If not, then you simply shouldn’t be able to sort this out in the context of space delimiters. It’s impossible short of things like recognizing data as names, etc. But if fields are comma delimited, and the names do not have commas, then you could import the data into one spreadsheet, and refer to that in another where the names are sorted out as needed.
Here’s an example that is meant to be pedantic about how it splits names, not optimized:
NameSplitter.ods (12.3 KB)
So, you would import only delimiting on commas. If you are curious about the functions on the Output sheet, look at them in the function wizard (the fx icon on the function bar).
Thanks!
I was able to use eeigor’s split-names formulas to accomplish the task.
Thanks again!
Import the data in one column and then split the data on the sheet.
split-names.ods (11.7 KB)
REGEX function is used, therefore LO Calc 6.2+ is required.
First Name: =REGEX($A2;"^.*(?= \w+$)")
Note: Extracts everything from the beginning of the A2 string to a space with the last word in the string (look-ahead assertion (?=…) is used).
Last Name: =REGEX($A2;"\w+$")
Note: Extracts the last word in the A2 string.
Thanks!
I was able to use eeigor’s split-names formulas to accomplish the task.
Thanks again!
Chaz