EG.

I want to only get the first name of the following full names that are
given in column A:

John M. Ford
Albert Cushy
Bertland Sunderland
Hugo Sanchez d'Angelo


My idea is to find how many spaces there is until the first " "
character and then use the mid function until this character (" ") to
create a new string with only the first name in it.

Anyone know how create a synthax like this?

Gabe.

FirstName = Left(FullName, InStr(FullName, " ") - 1)


"Gabe Tiger" wrote:

EG.

I want to only get the first name of the following full names that are
given in column A:

John M. Ford
Albert Cushy
Bertland Sunderland
Hugo Sanchez d'Angelo


My idea is to find how many spaces there is until the first " "
character and then use the mid function until this character (" ") to
create a new string with only the first name in it.

Anyone know how create a synthax like this?

Gabe.

Charlie's code is great...in this instance.

I found at times that VBA has problems with referencing 'a space' using
double quotes. Try creating the following and you'll get an error:

range("a1").formula = "=if(activesheet.name = "Sheet1",True, " ")"

By trying to input or search for a space using " " within a quoted line, VBA
crashes. For conistency I use the reference to Char(32), the ASCII reference
to a space.

Charlie's example would now look like the following:

FirstName = Left(FullName, InStr(FullName, Char(32)) - 1)


"Charlie" wrote:

FirstName = Left(FullName, InStr(FullName, " ") - 1)


"Gabe Tiger" wrote:

EG.

I want to only get the first name of the following full names that are
given in column A:

John M. Ford
Albert Cushy
Bertland Sunderland
Hugo Sanchez d'Angelo


My idea is to find how many spaces there is until the first " "
character and then use the mid function until this character (" ") to
create a new string with only the first name in it.

Anyone know how create a synthax like this?

Gabe.

The error comes from the fact that the formula has nested
quotes. It should be written as
Range("a1").Formula = _
"=if(activesheet.name = ""Sheet1"",True, "" "")"

(line split to avoid wrapping)

I've never known VBA to have any problems with spaces, and
for reasons of readability I'd strongly recommend against
using Chr(32) (not Char(32) BTW)


SyrNO wrote:
Charlie's code is great...in this instance.

I found at times that VBA has problems with referencing 'a space' using
double quotes. Try creating the following and you'll get an error:

range("a1").formula = "=if(activesheet.name = "Sheet1",True, " ")"

By trying to input or search for a space using " " within a quoted line, VBA
crashes. For conistency I use the reference to Char(32), the ASCII reference
to a space.

Charlie's example would now look like the following:

FirstName = Left(FullName, InStr(FullName, Char(32)) - 1)


"Charlie" wrote:

FirstName = Left(FullName, InStr(FullName, " ") - 1)


"Gabe Tiger" wrote:

EG.

I want to only get the first name of the following full names that are
given in column A:

John M. Ford
Albert Cushy
Bertland Sunderland
Hugo Sanchez d'Angelo


My idea is to find how many spaces there is until the first " "
character and then use the mid function until this character (" ") to
create a new string with only the first name in it.

Anyone know how create a synthax like this?

Gabe.

Problems with " " (literal space)? Me neither, but I do often come across
programmers who haven't yet discovered the "double-up" requirement. It
applies to apostrophes also, in the event one wants to reference a sheet name
that contains an apostrophe:

Sheet.Name = "Don't"

Range("'Don''t'!A1")="""Ok, I Won't"""


"Andrew Taylor" wrote:

The error comes from the fact that the formula has nested
quotes. It should be written as
Range("a1").Formula = _
"=if(activesheet.name = ""Sheet1"",True, "" "")"

(line split to avoid wrapping)

I've never known VBA to have any problems with spaces, and
for reasons of readability I'd strongly recommend against
using Chr(32) (not Char(32) BTW)


SyrNO wrote:
Charlie's code is great...in this instance.

I found at times that VBA has problems with referencing 'a space' using
double quotes. Try creating the following and you'll get an error:

range("a1").formula = "=if(activesheet.name = "Sheet1",True, " ")"

By trying to input or search for a space using " " within a quoted line, VBA
crashes. For conistency I use the reference to Char(32), the ASCII reference
to a space.

Charlie's example would now look like the following:

FirstName = Left(FullName, InStr(FullName, Char(32)) - 1)


"Charlie" wrote:

FirstName = Left(FullName, InStr(FullName, " ") - 1)


"Gabe Tiger" wrote:

EG.

I want to only get the first name of the following full names that are
given in column A:

John M. Ford
Albert Cushy
Bertland Sunderland
Hugo Sanchez d'Angelo


My idea is to find how many spaces there is until the first " "
character and then use the mid function until this character (" ") to
create a new string with only the first name in it.

Anyone know how create a synthax like this?

Gabe.