|
Posted to microsoft.public.excel.programming
|
|
|
how to do first name last name
http://office.microsoft.com/en-au/ex...001149851.aspx
On Wednesday, November 26, 2008 5:19 PM PhxDelt wrote:
Hi, I inherited a worksheet with a combined Name column. Some entries are
listed as "FirstName LastName" and others are "LastName, First Name ". How
do I split all of the names out into two new columns "LastName" and "First
Name"? Thanks.
On Wednesday, November 26, 2008 5:25 PM Mike wrote:
Hi,
The is no way Excel can know which is a first and which is a last name but
this will split the names
First part
=TRIM(LEFT(A1,FIND(" ",A1)))
Second part
=RIGHT(A1,FIND(" ",A1)+1)
Mike
"PhxDelta" wrote:
On Wednesday, November 26, 2008 5:35 PM PhxDelt wrote:
Hi Mike,
I figured I'd take a 2-step approach to this. All the "LastName, First
Name" are separated by a comma and a space. So how would I convert that
active cell to rearrange it to First Name space Last Name? Then I figured I
could loop through the whole column a second time and use what you wrote
below.
"Mike H" wrote:
On Wednesday, November 26, 2008 5:52 PM Mike wrote:
Hi,
You can't do that with code or a formula unless there is some regularly
repeating pattern in the frequency of first_name - last_name. for example
take the name
Michael Michael
Michael (my first name)
Michael ( a very common surname)
there is no way to programatically decide which is which is which. You can
do the split programatically by using the instr function and looping through
the names but will end up with the same problem as with the formula.
Mike
"PhxDelta" wrote:
On Wednesday, November 26, 2008 6:03 PM Gary Keramidas wrote:
here's what i'd do. i assumed all of the names are in column A, starting in row
1 on sheet1
Sub movenames()
Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
With ws.Range("A" & i)
If InStr(1, .Value, ",") Then
ws.Range("B" & i).Value = Trim(Split(.Value, ",")(1))
ws.Range("C" & i).Value = Trim(Split(.Value, ",")(0))
Else
ws.Range("B" & i).Value = Trim(Split(.Value, " ")(0))
ws.Range("C" & i).Value = Trim(Split(.Value, " ")(1))
End If
End With
Next
End Sub
--
Gary
"PhxDelta" wrote in message
...
On Wednesday, November 26, 2008 6:30 PM Ba wrote:
PhxDelta,
Assuming the sytax is always the same, then you could try this.
Last Name
=IF(ISERROR(LEFT(B2,FIND(",",B2,1)-1)),RIGHT(B2,LEN(B2)-(FIND("
",B2,1))),LEFT(B2,FIND(",",B2,1)-1))
First Name
=IF(ISERROR(RIGHT(B1,LEN(B1)-(FIND(",",B1,1)+1))),LEFT(B1,FIND("
",B1,1)+1-1),RIGHT(B1,LEN(B1)-(FIND(",",B1,1)+1)))
Cheers,
Bam
"Mike H" wrote:
On Thursday, November 27, 2008 7:33 AM PhxDelt wrote:
Thanks Gary and Bam,
Your answers worked perfectly although I went with Gary's so that I could
use a quick cut and paste macro versus doing it in the cells and dragging the
formulas over the whole range. Have a wonderful Thanksgiving, you've made
mine 100% better!
"Gary Keramidas" wrote:
On Thursday, November 27, 2008 7:39 AM PhxDelt wrote:
Thanks for your replies Mike. Your answers/formulas gave me enough to build
on but then Gary and Bamn finished it off. What I didn't explain well enough
to you was that there was a pattern, if the last name appeared first, it was
separated by a comma then space. When the first name appeared first, there
are no commas, just a space. Thanks again and Happy Thanksgiving!
"Mike H" wrote:
On Tuesday, October 12, 2010 1:53 PM tom kula wrote:
hey all you do to seperate first name/last name with a comma is go to data -- text to columns---delimited-- click next- then select only the comma check box and finish the wizard
Submitted via EggHeadCafe - Software Developer Portal of Choice
Review of DevExpress DXperience Control Suite
http://www.eggheadcafe.com/tutorials...rol-suite.aspx
|