Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there a way I can split last name first name in one cell
I just got a list of customers we need to write about a new store opening.
However, I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another.I would of course buy the program that will do that if there is no way to change that w/o a special program. O yes yes, how about stripping the duplicates out of the list, is there a way to do that as well. I'm an old guy, that used excel for a while, but I can't remember anything anymore, honestly...........Any help is appreciated............Peter |
#2
|
|||
|
|||
Before trying anything complicated, this might work for you:
1)If there is data to the right of the names column, copy the column to a new sheet 2)Select the list 3)DataText to columns Delimited........[Next] Check Comma, Space, and Treat consecutive delimters as one Click the [Finish] button. Does that help? Ron -- Regards, Ron "PeterM" wrote: I just got a list of customers we need to write about a new store opening. However, I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another.I would of course buy the program that will do that if there is no way to change that w/o a special program. O yes yes, how about stripping the duplicates out of the list, is there a way to do that as well. I'm an old guy, that used excel for a while, but I can't remember anything anymore, honestly...........Any help is appreciated............Peter |
#3
|
|||
|
|||
I will try that as soon as I get to work, many many thanks Ron, I will
report back.....Peter "Ron Coderre" wrote in message ... Before trying anything complicated, this might work for you: 1)If there is data to the right of the names column, copy the column to a new sheet 2)Select the list 3)DataText to columns Delimited........[Next] Check Comma, Space, and Treat consecutive delimters as one Click the [Finish] button. Does that help? Ron -- Regards, Ron "PeterM" wrote: I just got a list of customers we need to write about a new store opening. However, I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another.I would of course buy the program that will do that if there is no way to change that w/o a special program. O yes yes, how about stripping the duplicates out of the list, is there a way to do that as well. I'm an old guy, that used excel for a while, but I can't remember anything anymore, honestly...........Any help is appreciated............Peter |
#4
|
|||
|
|||
PeterM wrote:
I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another. If A1 contains the "last,first" text, then: TRIM(LEFT(A1,FIND(",",A1) - 1)) yields the last name, and TRIM(RIGHT(A1,LEN(A1) - FIND(",",A1))) yields the first name. The use of TRIM() is defensive in the first case. It is probably needed in the second case, unless you know there is no space after the comma. |
#5
|
|||
|
|||
Hello Ron, it worked , I can't tell you how much I appreciate you for your
wonderful help............May something real good happen to you soon, you made it happen for me.............Peter "PeterM" wrote in message ... I will try that as soon as I get to work, many many thanks Ron, I will report back.....Peter "Ron Coderre" wrote in message ... Before trying anything complicated, this might work for you: 1)If there is data to the right of the names column, copy the column to a new sheet 2)Select the list 3)DataText to columns Delimited........[Next] Check Comma, Space, and Treat consecutive delimters as one Click the [Finish] button. Does that help? Ron -- Regards, Ron "PeterM" wrote: I just got a list of customers we need to write about a new store opening. However, I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another.I would of course buy the program that will do that if there is no way to change that w/o a special program. O yes yes, how about stripping the duplicates out of the list, is there a way to do that as well. I'm an old guy, that used excel for a while, but I can't remember anything anymore, honestly...........Any help is appreciated............Peter |
#6
|
|||
|
|||
THank you Joe for your reply...........Ron's answer was so easy for me, it
worked right away. I do appreciate your reply very much.............Peter wrote in message oups.com... PeterM wrote: I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another. If A1 contains the "last,first" text, then: TRIM(LEFT(A1,FIND(",",A1) - 1)) yields the last name, and TRIM(RIGHT(A1,LEN(A1) - FIND(",",A1))) yields the first name. The use of TRIM() is defensive in the first case. It is probably needed in the second case, unless you know there is no space after the comma. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I split the y-axis scale on a chart? | Charts and Charting in Excel | |||
Can Columns be split? | Excel Discussion (Misc queries) | |||
How do I split a single cell in Excel ? | Excel Discussion (Misc queries) | |||
split a single column into 2 separate columns | Excel Worksheet Functions | |||
Advanced Window Split & Freeze Question | Excel Worksheet Functions |