Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
in excel how to get initial from name
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
in excel how to get initial from name
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 --- "thyagu" wrote: 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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
in excel how to get initial from name
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) -- Regards, OssieMac "thyagu" wrote: 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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
in excel how to get initial from name
Hi OssieMac, Thanks for answering my questions. it is very helpful to me.
thankyou with regards thyagarajan "OssieMac" wrote: 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) -- Regards, OssieMac "thyagu" wrote: 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
in excel how to get initial from name
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)&"." -- If this helps, please click the Yes button Cheers, Shane Devenshire "thyagu" wrote: Hi OssieMac, Thanks for answering my questions. it is very helpful to me. thankyou with regards thyagarajan "OssieMac" wrote: 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) -- Regards, OssieMac "thyagu" wrote: 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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
in excel how to get initial from name
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel calls a worksheet that has initial row consisting of labels | New Users to Excel | |||
How do I set initial Excel workbook name? | Excel Discussion (Misc queries) | |||
How can I have an initial zero in an Excel cell? | Excel Discussion (Misc queries) | |||
Format fonts (ie, initial cap) in Excel like you can in Word. | Excel Discussion (Misc queries) | |||
Remove middle initial from "first name middle initial" | Excel Discussion (Misc queries) |