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!

On Wed, 1 Jun 2005 19:33:02 -0700, Shadowofthedarkgod
wrote:

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

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

"Shadowofthedarkgod" wrote in
message ...
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!

On Wed, 1 Jun 2005 23:32:38 -0400, "Biff" wrote:

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

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" wrote in message
...
On Wed, 1 Jun 2005 23:32:38 -0400, "Biff" wrote:

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

On Thu, 2 Jun 2005 00:30:26 -0400, "Biff" wrote:

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

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 :)

On Thu, 2 Jun 2005 18:51:04 -0700, Shadowofthedarkgod
wrote:


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

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!

On Fri, 3 Jun 2005 23:24:01 -0700, Shadowofthedarkgod
wrote:


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

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

On Wed, 8 Jun 2005 19:32:03 -0700, Shadowofthedarkgod
wrote:



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