in excel how to get initial from name

[thyagu]

Hi, I want Initials of name in excel. Like in A1, I have
1) Lloyd L. Summers
2) Lloyd L Summers
I want L.L.S. in B1. Can I do that. Is there any formula?
if yes Then pls tell me.

 

[Max]

One way is to install & use the
UDF FrstLtrs below by Ron Rosenfeld (slightly adapted) ..
(UDF = user defined function)

To install the UDF:
Press Alt+F11 to go to VBE
Copy n paste the UDF into the code window (whitespace on right)
Press Alt+Q to get back to Excel

In Excel,
Assuming your data in A1 down,
put in B1: =frstltrs(A1)
copy down to extract the desired results

'--------
Function FrstLtrs(str As String) As String
Dim temp
Dim i As Long

temp = Split(Trim(str))

For i = 0 To UBound(temp)
FrstLtrs = FrstLtrs & Left(temp(i), 1) & "."
Next i

End Function
'-------

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[OssieMac]

Insert the following formula in B1.

Note it is one line even if it breaks in this post.

=LEFT(A1,1)&"."&MID(A1,FIND(" ",A1,1)+1,1)&"."&MID(A1,FIND(" ",A1,FIND("
",A1,1)+1)+1,1)

 

[thyagu]

Hi OssieMac, Thanks for answering my questions. it is very helpful to me.
thankyou

with regards
thyagarajan

 

[Shane Devenshire]

Hi,

Just two points,
1. You can shorten the first argument, and 2 of the FIND's
2. The answer is not complete - the period at the end is missing

=LEFT(A1)&"."&MID(A1,FIND(" ",A1)+1,1)&"."&MID(A1,FIND(" ",A1,FIND("
",A1)+1)+1,1)&"."

 

[Max]

Just a point to consider, thyagu ..

Should you have source names
which have only a single space or more than 2 spaces, eg:

Max Sommers
Peter G. Osgood Walthers

then the formula solution(s) provided would not suffice. You can test this
easily. Ron's UDF, as per my response will return correct results right
through.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---