Home |
Search |
Today's Posts |
#1
|
|||
|
|||
First letter of names, mi and last name concat, How?
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 |
#2
|
|||
|
|||
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 "Shadowofthedarkgod" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Biff" wrote in message ... 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 "Shadowofthedarkgod" wrote in message ... 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 |
#4
|
|||
|
|||
On Tue, 31 May 2005 18:30:01 -0700, Shadowofthedarkgod
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|