How do I get the first letter of a word in excel

[Guest]

I have this problem, see I want to create a script, I have the following
information available to me.

First name: John
MI: C
Last name: Doe

Now this is what I want; I want to convert this John C. Doe to "jcdoe". Also
I have similar problems to with dual last names, like dela toya, I have to
convert from juan r. dela toya to jrdelatoya. How do I do that? Thanks!!

 

[R.VENKATARAMAN]

=left("john",1)&"cdoe"

if john is in A1 you can use A1 instead of "john"

 

[Harald Staff]

Hi


There are no exact naming standards, so there is no bomb proof way to parse
names correctly. But if you have Outlook on your system, try this one:

http://www.dicks-blog.com/archives/2004/11/11/parse-names-with-outlook/

HTH. Best wishes Harald

 

[Harald Staff]

Sorry for clicking Send too fast. You may also need:
=LEFT(A1,1)
returns the first letter from A1.
=LOWER(A1)
converts to lowercase.
=SUBSTITUTE(A1," ","")
removes spaces.

HTH. Best wishes Harald

 

[Maurice]

From a quick search of the tips "Remove Space"

Problem:

The text in range B13:16 contains redundant parentheses.
How could we quickly remove them and set the format of the cells to
""general""?"

Solution:

In cell D13 enter the following formula:
=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(B13,"")"","""),""("","
"")),""General")
Copy the formula to cells D14:16 and then copy-paste special D13:16 as
values into B13:16.

Originial Text______Result
(Excel)_____________Excel
(Power point)_______Power point
(Access)____________Access
(Outlook)___________Outlook

The Formula:

=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(B13,")",""),"(","")),"General")

Will give you a good start

 

[mangesh_yadav]

Lets say you have the 3 parts of the name in cells A1, B1 and C1. Then
enter the following formula in cell D1

=LEFT(A1,1)&LEFT(B1,1)&SUBSTITUTE(C1," ","")

Mangesh

 

[Ragdyer]

This doesn't account for every possibility, but does take care of the
examples given ... plus a couple not mentioned:

=IF(ISERR(FIND(". ",A1)),LEFT(A1)&MID(SUBSTITUTE(A1," ",""),FIND("
",A1),25),LEFT(A1)&MID(SUBSTITUTE(SUBSTITUTE(A1,". ","")," ",""),FIND("
",A1),25))

Watch out for word-wrap!

 

[Bob Phillips]

Here is one way.

Enter this code in a standard code module

'---------------------------------------------------------------------
Private Function RESubString(Inp As String, _
Pattern As String, _
Optional N As Long = 0) As String
'---------------------------------------------------------------------
Dim oRegExp As Object, m As Object

On Error GoTo RE_error
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = Pattern
oRegExp.Global = True
Set m = oRegExp.Execute(Inp)
RESubString = IIf(m.Count > 0, m(N).Value, "")
GoTo RE_Exit
RE_error:
RESubString = "RE Error"
RE_Exit:
Set oRegExp = Nothing
On Error GoTo 0
End Function

'---------------------------------------------------------------------
Public Function ConvertName(nme As String)
'---------------------------------------------------------------------
' Function: Extracts the last name from a names string, and the other
' first letters
'---------------------------------------------------------------------
Dim sRegExp As String
Dim arynames
Dim sLastName As String
Dim sFirtsNames As String
Dim sTemp As String
Dim i As Long

sRegExp =
"\b([a-z]+\s+)*[A-Z](\w+\S?)*([-'][A-Z](\w+\S?)*)?\b(?=(\s+([JS]r\.?|[IVX]+)
)?\s*$|,)"
sLastName = Replace(RESubString(nme, sREgExp), " ", "")
sFirstnames = Left(nme, Len(nme) - Len(sLastName))
arynames = Split(sFirstnames, " ")
For i = LBound(arynames) To UBound(arynames)
sTemp = sTemp & Left(arynames(i), 1)
Next i
ConvertName = LCase(sTemp & sLastName)
End Function


and then use in the worksheet like so

=ConvertName(A1)

 

[Guest]

Thanks for all the reply. However, I forgot to add how about dual names or
even three names? Let's say, michelle carmen rose c. peters, it has to be
mcrcpeters. Thanks for the dual last names reply!