If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
TRIM spaces
I have first name and last name in one cell. Between the
first name and last name, there is a space. I want to put the first name in one cell and the last name in another cell. Is TRIM the right function to use? If not, how would I accomplish this? Thanks in advance. |
#2
|
|||
|
|||
TRIM spaces
Select the column with the names, then
Data TextToColumns Make sure "Delimited" is checked, then Next, Put a check in "Space", then Finish. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jh" wrote in message ... I have first name and last name in one cell. Between the first name and last name, there is a space. I want to put the first name in one cell and the last name in another cell. Is TRIM the right function to use? If not, how would I accomplish this? Thanks in advance. |
#3
|
|||
|
|||
TRIM spaces
One way:
Assuming there's only 2 parts to the names in col A, A1 down (First names and Last names only) Put in B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1) Put in C1: =MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,99) (the "99" in C1's formula is an arbitrary number, but should be sufficient. Adjust by increasing this number to one higher if necessary) Select B1:C1, and copy down Col B will return the 1st part and col C the 2nd part Example: If col A contains Ben Rag Jane Higgs Joe Gymars col B returns: Ben Jane Joe col C returns: Rag Higgs Gymars -- Rgds Max xl 97 --- Please respond in thread xdemechanik atyahoodotcom ---- "jh" wrote in message ... I have first name and last name in one cell. Between the first name and last name, there is a space. I want to put the first name in one cell and the last name in another cell. Is TRIM the right function to use? If not, how would I accomplish this? Thanks in advance. |
#4
|
|||
|
|||
TRIM spaces
One way:
Assuming there's only 2 parts to the names in col A, A1 down (First names and Last names only) Put in B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1) Put in C1: =MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,99) (the "99" in C1's formula is an arbitrary number, but should be sufficient. Adjust by increasing this number to one higher if necessary) Select B1:C1, and copy down Col B will return the 1st part and col C the 2nd part Example: If col A contains Ben Rag Jane Higgs Joe Gymars col B returns: Ben Jane Joe col C returns: Rag Higgs Gymars -- Rgds Max xl 97 --- Please respond in thread xdemechanik atyahoodotcom ---- "jh" wrote in message ... I have first name and last name in one cell. Between the first name and last name, there is a space. I want to put the first name in one cell and the last name in another cell. Is TRIM the right function to use? If not, how would I accomplish this? Thanks in advance. |
#5
|
|||
|
|||
TRIM spaces
Thanks so much. This worked like a cham!
"Ragdyer" wrote: Select the column with the names, then Data TextToColumns Make sure "Delimited" is checked, then Next, Put a check in "Space", then Finish. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jh" wrote in message ... I have first name and last name in one cell. Between the first name and last name, there is a space. I want to put the first name in one cell and the last name in another cell. Is TRIM the right function to use? If not, how would I accomplish this? Thanks in advance. |
Thread Tools | |
Display Modes | |
|
|