|
How To Separate First And Last Names Entered In One Cell Into Two Different Cells
|
|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
How To Separate First And Last Names Entered In One Cell Into Two Different Cells - Excel
|
View Answers
|
|
|
The database that I'm working on divides patient names into three separate columns: lastName, firstName, midInit. My department has several old spreadsheets that have data my boss suddenly wants included in my database. The trouble is, these old spreadsheets have the patient's last name, first name, and middle initial entered into the same column. There are at least 200 hundred names in these spreadsheets. I absolutely do not want to go back and separate the names into separate columns by hand.
Is there any way to get Excel to take the names entered into one cell and split them into three cells?
I've attached a spreadsheet example that shows what I need to do.
Similar Excel Video Tutorials
Names Cell If Name in Cell
- See how to name individual cells a name that is the cell content (the name of the cell is in the cell). To do this: 1) Put names in column B, 2) copy ...
Names in Formulas and Functions
- See how to use Names in Formulas and Functions AND how to edit Names. See how to name a cell. See how to name a range of cells. Learn about how Names ...
Names for Go To Quick Navigation
- See how to use Names cells to navigate quickly for one location to another in a workbook. This is a logical (beginning to end) story about ...
Similar Topics
I have 3 spreadsheets that have lists of students' names on them, hours they've been tutored, etc.. I want to pull out the first and last names (which are in two separate columns) of all kids that have been in all 3 spreadsheets into a separate sheet. Is this kind of thing possible?
Thanks, Robert
I've found several similar posts but nothing that will do exactly what I need. I have a list of several names that are in the form of FirstLast or FirstMiddleLast (no spaces, beginning of each name capitalized). I also have a separate file with these same names separated and put into 2 separate columns. I need to separate the FirstLast names into a firstname and lastname string so that I can search for exact matches in the other file with the names already in separate columns which I can do once I get the names separated.
To complicate issues, some first names are initials like J.J., and some people do not have middle names, and some middle names are initials. So I would need to be able to come up with strings for each of the following examples:
JohnHarris
ScottLeeBrown
MikeP.Smith
J.J.Robinson
JamesS.P.Black
that return:
First Last
John Harris
Scott Lee Brown
Mike P. Smith
J.J. Robinson
James S.P. Black
The forum deleted the formatting, but the last name will always just be one name and the first will be everything else.
One way I was thinking that may be easiest (if possible) is to search for the last capital letter and turn the rest of the string following it into the lastname and then take the rest and put it into the first name, then separate the first from the middle name in that string. Thanks in advance for any help.
Hello All,
I have an excel file that contains names of people. There is one column that contains the first name, middle initial, and the last name all in one cell. On one line there may be the first name, middle initial, and last name. On another line there is just the first name and last name. The names are separated by spaces. They do not run all together. I want to separate the first name and last name into 2 different cells and eliminate the middle initial. I don't think I can do text to columns because the names are kinda staggered due to the middle initial. Is there a way to do this? Any suggestions would be great.
Thanks in advance,
psu0123
I need some help with names.
I have a list of names as follows:
Smith, John A
Jones, Jane D
How do I separate these into columns. I know how to get the middle initial into a separate column, but what I really need is last name in a column, first name in a column and then middle initial. From there I can write a formula to give me the user ID info I need, first initial of the first name, the middle initial and the first four initials of the last name.
I just need help separating the names into columns.
Thanks !!
I want to separate names, example, Doe, John D., that are entered in one cell.
How to separate them into 2 or 3 cells?
Hi, I am new and would be most grateful if someone could tell me how to solve a problem. I have a spreadsheet with approximately 1100 names that I use for a mail merge. I have separate spreadsheet including about 200 of those same names. I don't want the 200 names in the smaller spreadsheet to be included in the mail merge. How can I compare the two and eliminate the 200 names from my master spreadsheet? Thank you, Donna
My names in my file are inserted in one column as lastname, firstname. I need to separate them and put them in two different columns. "find" doesn't seem to work. My version of excel 2000 just gives me a message that it isn't supported. Any ideas on how to find the ", " and separate the first and last names without using "find"?
I tried: right(clientname, len(clientname))-find(", ",(clientname))
didn't work.
Hey all,
I am having some difficulty coming up with an excel formula that splits last name, first name, and middle initials in separate columns. There's variation where the middle initial may be one letter or an entire word and sometimes the character "&" is tossed at the end of column:
CAGLE,DANNY D &
SNEIR,LARRY &
ALONSO,MERRIL M
KIM,JIN SOO &
HARRISON,GEORGE & NANCY
LUKS,BARBARA VACCARO
MILES,JAMIE & MARLON D
LITT,JEFFREY D & LISA J
Is there an excel calculation to get last, first, and middle initial (whether one character or several) in their own columns while getting rid of the "&" unless it's between two first names? So basically the "&" goes away if it's the very last character. Note that if there's two first names, then the "&" would stay and only the initial of righter most name would be split in own column.
Thanks for any response
I have a list of Full names (John A Doe) and am trying to separate the cells in First, Middle, and Last names. Some do not have middle initials and some have double last names. How do I separate out the names?
Hi-light the column.
Data -> Text to Columns...
Answer the resulting wizard
--
Gary's Student
"Dwight in Georgia" wrote:
> My list has the first and last names in the same cell. How can I separate
> these into two separate cells so I can sort by last name. Or is there a way
> to sort by the last name when the name in the cell is "firstname lastname"?
Please advise on how to separate a text string that has no delimiters? The cells contain names without spaces or commas in this format: FirstnameLastname. The result I want is two columns with firstname in one and lastname in the second column. The only thing distinguishing the two desired fields within the string is an upper case letter at the beginning of first and last names.
Thanks!
I have two very large Excel files that have literally hundreds of names. The names reside in Column G in both the spreadsheets. I'd like to use a third spreadsheet that has names in it that I choose. Then those names will compare what is in the two original spreadsheets cut out all the names not appearing in the third spreadsheet. This way I can center around the dozen or so names I'm interested in each day. Your help very much appreciated.
Tony
Hi -
i have a column A that contains customer names, last and first. They are separated by a comma and a space:
"Last, first"
I would like to separate the first and last names into 2 separate columns. Obviously the # of characters will vary from row to row. Is there a way to separate the two?
Jill
Hey all,
I am having some difficulty coming up with an excel formula that splits last name, first name, and middle initials in separate columns. There's variation where the middle initial may be one letter or an entire word and sometimes the character "&" is tossed at the end of column:
CAGLE,DANNY D &
SNEIR,LARRY &
ALONSO,MERRIL M
KIM,JIN SOO &
HARRISON,GEORGE & NANCY
LUKS,BARBARA VACCARO
MILES,JAMIE & MARLON D
LITT,JEFFREY D & LISA J
Is there an excel calculation to get last, first, and middle initial (whether one character or several) in their own columns while getting rid of the "&" unless it's between two first names? So basically the "&" goes away if it's the very last character. Note that if there's two first names, then the "&" would stay and only the initial of righter most name would be split in own column.
Thanks for any response
Hey all,
I am having some difficulty coming up with an excel formula that splits last name, first name, and middle initials in separate columns. There's variation where the middle initial may be one letter or an entire word and sometimes the character "&" is tossed at the end of column:
CAGLE,DANNY D &
SNEIR,LARRY &
ALONSO,MERRIL M
KIM,JIN SOO &
HARRISON,GEORGE & NANCY
LUKS,BARBARA VACCARO
MILES,JAMIE & MARLON D
LITT,JEFFREY D & LISA J
Is there an excel calculation to get last, first, and middle initial (whether one character or several) in their own columns while getting rid of the "&" unless it's between two first names? So basically the "&" goes away if it's the very last character. Note that if there's two first names, then the "&" would stay and only the initial of righter most name would be split in own column.
Thanks for any respons
Hi,
sorry if this is a newbie question.
I have 2 spreadsheets each having one column of names and several columns of data for each name. Many of the names are the same, but the columns do not match exactly. I am trying to merge the two spreadsheets so that there is one column with all the names from both spreadsheets with the corresponding data for the names. However, I cant figure out how to make excel recognize the identical names and put all the data from both spreadsheets on the same row with that name.
Ex:
spreadsheet 1:
Steve 343 45 23
Paul 32 3 23
Frank 223 23 12
spreadsheet 2:
Tom 33 76 6
Steve 23 34 3
Paul 1 334 45
I would like to combine the data so that all the names are shown, but Steve and Paul are only shown once. There would be six columns of data, so I dont need that combined.
Does anyone know how to do this?
I appreciate your help.
I'm working with names in a single column that appear as shown below
C1 = Robert Jones
C1 = Robert, Jones
I want to change the order so names appear as lastname,first name for both situations. Using the first example, a comma would need to be added once the order is switched.
I searched Google, but it only shows how to split the first and last names into separate columns. I want to keep everything in the same column.
Any suggestions on how to do this? Can I do this with a formula or do I need to write it as VBA?
Thanks,
Tom
I have a problem with seperating text in a cell. I have a database with people in the following format:
LAST NAMES First Names
LASTNAME First Name
LASTNAME Firstname
etc.
The last name of the person is in capitals, from the first name only the first letter is a capital. I want to seperate first and last name into two seprate columns. The problem is (and why i cant use text to columns) is that an individual can have multiple first or last names and I want all last names in a cell and al first names in a cell.
Does anybody have any idea as to how to solve this (preferrably in a formula?)
I'm working on budgets (salaries). I have one main spreadsheet with all employee names listed - other spreadsheets are for each department. I need to bring the total salary from the department spreadsheets and would like to do this by looking up (or matching?) the names in the column from the main sheet to the department sheets and then putting the salary from the department spreadsheet to the main spreadsheet. In addition, I have several that have their salary split between two or more departments. On my department spreadsheet I have a column labeled % of salary for this department. On the main spreadsheet, I want to figure out a formula that will sum the percentages for a certain person (so I make sure I don't go over 100%).
For example - Joe Smith's name is on the main employee spreadsheet. He is also listed on 3 other department spreadsheets, with % of salary listed as 30% for department 1, 30% for department 2, and 40% for department 3. On the main spreadsheet I need to add up Joe's percentages from the department spreadsheets.
Can someone help me? Thanks!
Ok, I have a spreadsheet that a client gave me that will be used for a Data Merge. It lists Patient Names/Addresses as well as their respective Doctors. Most all of the Doctors listed have multiple Patients.
ie:
Patient Name | Patient Address | Doctor Name | Doctor Address
Mary | NY | Dr. Ross | NY
John | NY | Dr. Ross | NY
Tim | NY | Dr. Ross | NY
Jane | MI | Dr. Moore | MI
Jacob | MI | Dr. Moore | MI
The Patient name/address is in the same row as their Doctor's name/address, so I basically have multiple records of the Doctor for each patient. The merged letter that I need the data for will have the Doctor's name/address at the top of the letter and ALL of their Patient's names/address at the end of the letter.
My question is this: How do I automate Excel to see the Doctor's names and then list all of the Patient names attached to said Doctor without reading the wrong Patient names? (the patient names are listed first by the way)
The letter will be formatted like this:
-------------------------------------------
<Doctor Name>
<Address>
Here is the text of the letter...
<Patient 1>
<Patient 2>
<Patient 3>
-------------------------------------------
I have attached a sample with only a few names. The actual list is about 1000 different names.
Any help with this matter would be greatly appreciated.
In a spreadsheet containing a long list of names, with the last, first, and
middle names in separate columns, what is the easiest way to replace each
middle name with only a middle initial?
Hi guys
I have a column of user names eg
Joe Bloggs
Adam barry Smith
etc
How can I separate this into just first names and last names (ie ignoring any middle names) I can get the first name using
left(a1,find(" ",a1)-1)
but can't work out how to do the same for the last name
Cheers
Jim
This is where you introduce yourself? I make lists for my gf's dad's company using excel. One of my most excruciating tasks is to separate the first and lasts names from one cell into two cells. Does anyone know a quicker way to do this other than manually? I have 10,000 names to do and it's going to be hours if I do it by hand. Hello and thanks for readin'.
I have names listed in separate columns, but I need them to be in one column and show as Last Name, First Name. I know how to separate names (Text To Columns), but how do you put them together? Is there something that does it, or could a formula do it? Help! I have a huge list of names and I don't want to retype them.
I have data in 2 worksheets, say WS1, WS2
WS1 contains following plus other data (here i used : to separate cells)
--------------------------------------
FirstName : LastName : UserID
--------------------------------------
Serine S H : Koay : WKOAS
Roger : Groebi : WGROR
Jill : Neubronner : WNEUJ
Su Ann : Low : WLOWV
--------------------------------------
WS2 contains following plus other data (here i used : to separate cells)
--------------------------------------
FirstName : LastName : UserID
--------------------------------------
Serine : Koay :
Roger A : Groebi :
Neubronner : Jill :
Su : Ann Low :
--------------------------------------
I need to find userID in WS2 for corresponding names from WS1. I joined First and Lastnames in both the worksheets, used
VLOOKUP and got the userid in WS2, if the names matches exactly (not shown here). But if the names doesn't match exactly, as
above, VLOOKUP doesn't work. Eventhough we can see the names are there, but either contain additional/less initials,
reversed first and last name etc.
Is there any other way I can get the result?
|
|
