VBA routine to assign first name only to a variable

[fitful_thought]

Hi,

Cells(1, 1) contains the value
Annette Thompson

How do I get the variable fName to = Annette?

Any help at all will be much appreciated.

 

[Frank Kabel]

Hi
one way:

dim rng as range
dim fname
dim fullname
dim pos as integer

set rng = activesheet.cells(1,1)
fullname=rng.value
pos = instr(fullname, " ")
if pos>0 then
fname=left(fullname,pos-1)
else
fname=""
end if
msgbox fname

 

[Frank Kabel]

Hi
one way:

dim rng as range
dim fname
dim fullname
dim pos as integer

set rng = activesheet.cells(1,1)
fullname=rng.value
pos = instr(fullname, " ")
if pos>0 then
fname=left(fullname,pos-1)
else
fname=""
end if
msgbox fname

 

[JulieD]

Hi David

fname = left(cells(1,1),instr(cells(1,1)," ")-1)

Cheers
JulieD

 

[fitful_thought]

Thanks JulieD,
Works like a charm.

 

[Tom Ogilvy]

if you have xl2000 or later

fname = Split(Cells(1,1)," ")(0)

 

[fitful_thought]

Thanks Tom,

I have 2002.
Your method is very concise.
Thank you.