automatically work out initial from first name

[Database User]

Is there a formula that can automatically look at the firstname field and
work out the initial or initials if there are 2 names?

 

[Allen Browne]

Left() grabs the first character.

You could then use Instr() to locate any space, and Mid() to grab the next
character after the space.

Alternatively, if you want to handle more than 2 names, you could use
Split() to create an array of names, and Left() as you loop through them.

 

[Database User]

Sorry if i'm replying to wrong place i'm not sure where else to apply but
could you please explain - i'm not sure how to write this formula i tried
this but doesn't work

=Left(([Client First Name]))

but tells me that the expression entered has wrong number of arguments.
Could you please write the formula out for a 2 letter initial thanks so much!

 

[Allen Browne]

You must enter the 2nd argument for Left(), e.g.:
=Left([Client First Name], 1)

You may need to learn more about these functions and VBA in general before
you will be able to parse the following name(s) from the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sorry if i'm replying to wrong place i'm not sure where else to apply but
could you please explain - i'm not sure how to write this formula i tried
this but doesn't work

=Left(([Client First Name]))

but tells me that the expression entered has wrong number of arguments.
Could you please write the formula out for a 2 letter initial thanks so
much!

Left() grabs the first character.

You could then use Instr() to locate any space, and Mid() to grab the
next
character after the space.

Alternatively, if you want to handle more than 2 names, you could use
Split() to create an array of names, and Left() as you loop through them.

 

[Database User]

Thanks i guess i don't know enough! The formula below worked for the first
letter but could you write me the full thing for a 2 name firstname.
Thanks

You must enter the 2nd argument for Left(), e.g.:
=Left([Client First Name], 1)

You may need to learn more about these functions and VBA in general before
you will be able to parse the following name(s) from the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sorry if i'm replying to wrong place i'm not sure where else to apply but
could you please explain - i'm not sure how to write this formula i tried
this but doesn't work

=Left(([Client First Name]))

but tells me that the expression entered has wrong number of arguments.
Could you please write the formula out for a 2 letter initial thanks so
much!

Left() grabs the first character.

You could then use Instr() to locate any space, and Mid() to grab the
next
character after the space.

Alternatively, if you want to handle more than 2 names, you could use
Split() to create an array of names, and Left() as you loop through them.


Is there a formula that can automatically look at the firstname field
and
work out the initial or initials if there are 2 names?

 

[Allen Browne]

... could you write me the full thing ...

No. We can't just write your code for you.

 

[John Vinson]

Is there a formula that can automatically look at the firstname field and
work out the initial or initials if there are 2 names?

Hrm. Fun one...

Left([firstname], 1) & IIF(InStr([firstname], " ") > 0,
Mid([Firstname], InStr([firstname], " ")) + 1, 1)

should work assuming a) there are only one or two names and b) there's
only one blank between them.

John W. Vinson[MVP]

 

[Database User]

Thanks i'm on the right track but with that formula i get the first initial
and a number 1 next to it - what could be the correct formula. I played
around with it a bit but the best i could get was the initial of first name

and then the complete first name i.e. both names =Left([Client First
Name],1) & IIf(InStr([Client First Name]," ")>0," " & Mid([Client First
Name],1))

Would you know th correct one - thanks for your help!

Is there a formula that can automatically look at the firstname field and
work out the initial or initials if there are 2 names?

Hrm. Fun one...

Left([firstname], 1) & IIF(InStr([firstname], " ") > 0,
Mid([Firstname], InStr([firstname], " ")) + 1, 1)

should work assuming a) there are only one or two names and b) there's
only one blank between them.

John W. Vinson[MVP]

 

[John Vinson]

Thanks i'm on the right track but with that formula i get the first initial
and a number 1 next to it - what could be the correct formula. I played
around with it a bit but the best i could get was the initial of first name

and then the complete first name i.e. both names =Left([Client First
Name],1) & IIf(InStr([Client First Name]," ")>0," " & Mid([Client First
Name],1))

Just missing part of the expression:

=Left([Client First Name],1) & IIf(InStr([Client First Name]," ")>0,
" ", Mid([Client First Name],InStr([Client First Name]," ")+1, 1))

John W. Vinson[MVP]

 

[Database User]

I just tried that but it gives me:

Anyone with firstname just one name e.g peter result is PP and anyone who
has 2 names in firstname e.g Peter Henry the result is just P

Can you work it out?!!
Thanks

Thanks i'm on the right track but with that formula i get the first initial
and a number 1 next to it - what could be the correct formula. I played
around with it a bit but the best i could get was the initial of first name

and then the complete first name i.e. both names =Left([Client First
Name],1) & IIf(InStr([Client First Name]," ")>0," " & Mid([Client First
Name],1))

Just missing part of the expression:

=Left([Client First Name],1) & IIf(InStr([Client First Name]," ")>0,
" ", Mid([Client First Name],InStr([Client First Name]," ")+1, 1))

John W. Vinson[MVP]

 

[John Vinson]

I just tried that but it gives me:

Anyone with firstname just one name e.g peter result is PP and anyone who
has 2 names in firstname e.g Peter Henry the result is just P

Can you work it out?!!

Sorry... typo.

Should be

=Left([Client First Name],1) & IIf(InStr([Client First Name]," ")=0,
"", Mid([Client First Name],InStr([Client First Name]," ")+1, 1))

It might help if you looked at the online help and figure out what the
function is doing, instead of just blindly using it. The IIF statement
has three arguments - if the first one evaluates to a TRUE expression
it returns the second, and if it's false, the third. I had

0

so it was returning a blank if there was a blank in the name (InStr
returns the position of the blank) and the initial if it didn't - the
reverse of what I intended. Either swapping the "" and the Mid()
functions, or changing the >0 to =0 will solve it.

John W. Vinson[MVP]