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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany

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!


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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


--
Regards
Frank Kabel
Frankfurt, Germany

I would also like to know, suppose Cell ntain telphone number, it can
be 1 number only or 2 number, if there are 2 telephone number, it
will be 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
separate
them if there are 2 number~

Thx!


---
Message posted from http://www.ExcelForum.com/

I have solved all the problem !!!! Thx everyone hepling me!

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy

"michelle_ho " wrote in message
...
I would also like to know, suppose Cell ntain telphone number, it can be
1 number only or 2 number, if there are 2 telephone number, it will be
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 separate
them if there are 2 number~

Thx!


---
Message posted from http://www.ExcelForum.com/

Michelle

One way:

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

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"michelle_ho " skrev i en
meddelelse ...
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!


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany

Thx for all's replies!

It works!

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


---
Message posted from http://www.ExcelForum.com/

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




"michelle_ho " wrote in message
...
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!


---
Message posted from http://www.ExcelForum.com/