First letter of names, mi and last name concat, How?

[Guest]

I have the following input:

First name: John Michael or John
MI: C
Last name: Dela Toya or Doe

Now what I need is to convert John Michael C Dela Toya to jmcdelatoya, or
John C Doe to jcdoe. I'm having trouble in making excel count the number of
words in a name, since if my input has 3 first names, it has to have 3
initials,like this: Mary Jane Carmen D. Roberts to mjcdroberts. Can anyone
help me pls? Thanks

 

[Biff]

Hi!

Only tested with 2 word names. Will not work with 3 or more word names.

A1 = John Michael or John
A2 = C
A3 = Dela Toya or Doe


=LEFT(A1,1)&IF(ISERROR(FIND(" ",A1)),"",MID(A1,FIND("
",A1)+1,1))&A2&SUBSTITUTE(A3," ","")

Returns:
JMCDelaToya

OR:

JCDoe

Biff

 

[Biff]

If you want the result to be in all lower case:

=LOWER(LEFT(A1,1)&IF(ISERROR(FIND(" ",A1)),"",MID(A1,FIND("
",A1)+1,1))&A2&SUBSTITUTE(A3," ",""))

Biff

 

[Ron Rosenfeld]

I have the following input:

First name: John Michael or John
MI: C
Last name: Dela Toya or Doe

Now what I need is to convert John Michael C Dela Toya to jmcdelatoya, or
John C Doe to jcdoe. I'm having trouble in making excel count the number of
words in a name, since if my input has 3 first names, it has to have 3
initials,like this: Mary Jane Carmen D. Roberts to mjcdroberts. Can anyone
help me pls? Thanks

It's relatively simple to have the initials of all the names and except spell
out the last and have it all in lower case. A short UDF:

========================
Function NameCode(Names As String) As String
Dim Nm
Dim i As Long

Nm = Split(Names)
For i = 0 To UBound(Nm) - 1
NameCode = NameCode & Left(Nm(i), 1)
Next i

NameCode = LCase(NameCode & Nm(i))

End Function
======================

However, with a name like John Dela Toya, where Dela Toya is the last name, I
know of no rule that can differentiate that from John Dela Toya, where Dela is
the middle name.

So you need to develop a method of determining when the last two names in the
string represent a last name, vs a middle and last name.




--ron