Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add a dot........please help
Hi,
I have a huge data of customers names. I want to take out the first letter of each word in the name and add with a dot. Is there any formula? Example:- Tiger Woods as T.W. Sachin Tendulkar R as S.T.R. Adam John Gilly as A.J.G. Pointing Jr. as P.Jr. -- Thanks and all your help will be much appriciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to add a dot........please help
Hi Pradeep
Try this code. It assumes that your list of names are in column A starting at A1, and it will output the result to column B Sub ExtractInititials() Dim lr As Long, i As Long, j As Long Dim s As Variant lr = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To lr s = Split(Cells(i, 1), " ") For j = 0 To UBound(s) If Right(s(j), 1) < "." Then Cells(i, 2) = Cells(i, 2) & Left(s(j), 1) & "." Else Cells(i, 2) = Cells(i, 2) & s(j) End If Next j Next i End Sub To install Copy code above Alt + F11 to enter VB Editor Alt+M+I to Insert Module Paste code into white pane that appears Alt+F11 to return to Excel To Use Alt+F8 to select Macros Highlight macro Name Run -- Regards Roger Govier "Pradeep" wrote in message ... Hi, I have a huge data of customers names. I want to take out the first letter of each word in the name and add with a dot. Is there any formula? Example:- Tiger Woods as T.W. Sachin Tendulkar R as S.T.R. Adam John Gilly as A.J.G. Pointing Jr. as P.Jr. -- Thanks and all your help will be much appriciated __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|