FIRST letter of the names, mi and the whole last name.

[Guest]

I have the following input:

First name: John Michael or John or Mary Jane Carmen
MI: C
Last name: Dela Toya or Doe or Roberts

Now what I need is to convert John Michael C Dela Toya to jmcdelatoya, or
John C Doe to jcdoe and mjcdroberts. 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 C. Roberts to mjcdroberts. Can anyone
help me pls?

Mr. Biff gave me a formula, however the formula takes the FIRST TWO LETTERS
of the name and is not applicable with 3 or more word names. I need the first
letter of the name. The middle initial is ok, and the last name is already
fixed, I just need the FIRST LETTER of the NAME or NAMES. Thanks!

 

[Ron Rosenfeld]

I have the following input:

First name: John Michael or John or Mary Jane Carmen
MI: C
Last name: Dela Toya or Doe or Roberts

Now what I need is to convert John Michael C Dela Toya to jmcdelatoya, or
John C Doe to jcdoe and mjcdroberts. 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 C. Roberts to mjcdroberts. Can anyone
help me pls?

Mr. Biff gave me a formula, however the formula takes the FIRST TWO LETTERS
of the name and is not applicable with 3 or more word names. I need the first
letter of the name. The middle initial is ok, and the last name is already
fixed, I just need the FIRST LETTER of the NAME or NAMES. Thanks!

If you would respond to the question I asked you in your original thread, I
might be able to help.

In general, it is better to keep in the original thread. It saves having to
repeat things and so is more efficient.


--ron

 

[Biff]

Hi!

This one will work with up to 3 first names:

=LOWER(LEFT(A1,1)&IF(ISERROR(FIND(" ",A1)),"",MID(A1,FIND("
",A1)+1,1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))>1,MID(A1,FIND("~",SUBSTITUTE(A1,"
","~",2))+1,1),"")&A2&SUBSTITUTE(A3," ",""))

If you might have 4 first names (!!!!!) we could put another chunk of
formula in there but as you can see, the formula is already getting kind of
juicy!

Try Ron's UDF!

Biff

 

[Ron Rosenfeld]

Try Ron's UDF!

The problem for the OP remains as I previously stated. How do you
differentiate a non-hyphenated two word last name from a middle name + last
name?

Perhaps some kind of lookup table? But I just don't have any information about
this.


--ron

 

[Biff]

Hi Ron!

I'm "working" under the assumption that this data is in 3 cells.

first names(s)
middle inital
last name(s)

If you were to modify the UDF to return ALL first letter initals then at
least it could be used on the first name(s) cell then the mi and last
name(s) are easy enough.

Biff

 

[Ron Rosenfeld]

I'm "working" under the assumption that this data is in 3 cells.

first names(s)
middle inital
last name(s)

If you were to modify the UDF to return ALL first letter initals then at
least it could be used on the first name(s) cell then the mi and last
name(s) are easy enough.

I was working on the assumption that the entire name was in a single cell. If
the last name is in a separate cell, then things would be simple. Actually, if
there were a lookup table, or any coherent rule to know when the last two words
were all "last name", it would be simple.

I'll wait for a response from the OP, hopefully in the same thread, before
going further, though.


--ron

 

[Guest]

They are in 3 different fields, it is easier to input data for me like that
and to avoid confusion. And I'm sorry bout posting another one, its just that
there are so many posts in here that I'm afraid that my post will just be
buried in there. They should make this one in the forums where the newest
post will be in front. I'm yet to try the formulas that you gave me, but
thanks anway

 

[Ron Rosenfeld]

They are in 3 different fields, it is easier to input data for me like that
and to avoid confusion. And I'm sorry bout posting another one, its just that
there are so many posts in here that I'm afraid that my post will just be
buried in there. They should make this one in the forums where the newest
post will be in front. I'm yet to try the formulas that you gave me, but
thanks anway

It is more likely that your new thread will be missed by those who are thinking
about your problem, than it is that we might miss a post of yours indicating a
problem with a previous solution.

My UDF will not work on a three cell type of entry. However, the following UDF
will work on that kind of entry. Not knowing if the three cells would be
contiguous or not, I wrote the UDF so that either contiguous or non-contiguous
cells could be used. Any number of names can be entered in each cell. The UDF
will pick up the initials of each name in the first two cells; for the third
cell it will strip out the spaces. The output will be all lower case.

To enter this UDF, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, enter =NameCode(cell_refs) into some cell where cell_refs
refers to either a three cell range, or three separate cells containing the
first name(s), middle name(s) and last name(s).



==========================
Option Explicit

Function NameCode(rg, Optional rg2, Optional rg3) As String
Dim c As Range
Dim i As Long, j As Long
Dim Nm(2) As String
Dim Temp

If rg.Count <> 3 Then
If IsMissing(rg2) Or IsMissing(rg3) Then
MsgBox ("Not enough names")
Exit Function
End If
End If


If rg.Count = 3 Then
For Each c In rg
Nm(i) = c
i = i + 1
Next c
Else
Nm(0) = rg
Nm(1) = rg2
Nm(2) = rg3
End If


For j = 0 To 2
Temp = Split(Nm(j))

If j = 0 Or j = 1 Then
For i = 0 To UBound(Temp)
NameCode = NameCode & Left(Temp(i), 1)
Next i

Else
For i = 0 To UBound(Temp)
NameCode = NameCode & Temp(i)
Next i
End If
Next j

NameCode = LCase(NameCode)

End Function
==================================
--ron

 

[Guest]

Thanks! Cool code, but its giving me an error "Compile error: Expected: line
number or label or statement or end of statement."

How do I input the cell anyway? is it =NameCode(A1,A2,A3) or =NameCode(A1:A3)?
I'd reallylike to try our your code. Thanks again!

 

[Ron Rosenfeld]

Thanks! Cool code, but its giving me an error "Compile error: Expected: line
number or label or statement or end of statement."

I don't get that. Perhaps your news reader has introduced an extraneous line
feed or character. Post back exactly what you've got pasted into the Excel
module, and I'll see if I can spot the problem.

How do I input the cell anyway? is it =NameCode(A1,A2,A3) or =NameCode(A1:A3)?

I wrote the code so that either should work; as should substituting strings for
the references.

I'd reallylike to try our your code. Thanks again!

--ron

 

[Guest]

Thanks, got it already, great work!!! I included the line ==========,
thought it would just be a comment much like on vbs. Thanks again

 

[Ron Rosenfeld]

Thanks, got it already, great work!!! I included the line ==========,
thought it would just be a comment much like on vbs. Thanks again

Oh good, thanks for posting back.

In VBA, a comment is denoted by a leading apostrophe or single quote. It can
appear anywhere on the line and all after it, on the same line, will be treated
as a comment.

Sorry about that. I use the ============ to set off the macro from the rest of
my note.


--ron