How to separate First name and Last Name

  • Thread starter Thread starter michelle_ho
  • Start date Start date
M

michelle_ho

Suppose in cell D3, the value is "Christine White".

I have two variables, firstName and lastName as String.

How can I separate "Christine White", so that firstName = "Christine
and lastName = "White"??

Thx a lot
 
I would also like to know, suppose Cell ntain telphone number, it can b
1 number only or 2 number, if there are 2 telephone number, it will b
separeted by a "/"

For example:

F5 = 11223344
or
F5 = 11111111 / 44444444

Then how can i know it has 1 or 2 phone number, and how can i separat
them if there are 2 number~

Thx
 
Hi
one way:

sub foo()
Dim firstName as string
Dim lastName as string
Dim rng as range
set rng = activesheet.range("D3")
with rng
firstname=left(.value,instr(.value," ")-1)
lastname=mid(.value,instr(.value," ")+1,255)
end with
end sub
 
Michelle

One way:

FirstName = Split([D3]," ")(0)
LastName = Split([D3]," ")(1)

To use this solution, you must have
Excel 2000 or later.
 
Hi Michelle

if you just want to cycle through your records and split them into two
columns then this can be done using the Text to Columns feature on the Data
menu

if you're writing code and need to split the two up then one way is:

dim i as integer
dim firstName as string
dim lastName as string
dim fullName as string

fullName = Range("A1") 'or whatever to get the fullname
i = InStr(fullName, " ") 'returns the number of character that the space
relates to
firstName = left(fullName, i - 1) 'take the left portion of fullname
lastName = right(fullName, len(fullName) - i) 'take the right portion of
fullname

assumes - all names are in the first name / last name structure and that the
full names only have one space in them, hope this helps

Cheers
JulieD
 
Thx for all's replies!

It works!

I am using Frank Kabel 's suggestion. But I want to know, what if th
user didn't enter firstname, then how can I detect 1 words, or 2 word
entered by the user
 
Hi
try

sub foo()
Dim tel_1 as string
Dim tel_2 as string
Dim rng as range
set rng = activesheet.range("D3")
with rng
if instr(.value,"/")>0 then
tel_1=left(.value,instr(.value,"/")-2)
tel_2=mid(.value,instr(.value,"/")+2,255)
else
tel_1 = .value
end if
end with
end sub
 
Dim iloc as Long
if instr(activecell.Value,"\") then
iloc = Instr(activeCell.Value,"\")
firstnumber = Left(activecell.value,iloc-1)
secondnumber = Right(ActiveCell.Value, len(activecell.value)-iloc)
Else
firstnumber = ActiveCell.Value
secondnumber = ""
End if
 
Hi
try
sub foo()
Dim firstName as string
Dim lastName as string
Dim ofind as integer
Dim rng as range
set rng = activesheet.range("D3")
with rng
ofind = instr(.value," ")
if ofind then
firstname=left(.value,ofind-1)
lastname=mid(.value,ofind+1,255)
else 'only one name entered
lastname = .value
end if
end with
end sub
 
Back
Top