Need to add a dot........please help

  • Thread starter Thread starter Pradeep
  • Start date Start date
P

Pradeep

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.
 
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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top