# Extracting Initials in Excel Again

jaraba 05:23 29 Jul 2010
Locked

I marked my original post as resolved too early.
I want to extract the initials of surnames in one column in excel to another.
VoG posted a formula "=LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)" which worked with 2 surnames in a column but threw up a "#value!" error when there is only 1 surname in the column.
A formula to fix this would be greatly appreciated.

VoG II 07:44 29 Jul 2010

Try

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

jaraba 09:33 29 Jul 2010

Thanks.
It works with 2 surnames but although I have tried expanding the formula can't get it to work with 3 or 4, which some people have in my excel db.
More help is needed by this useless individual.

Terry Brown 09:40 29 Jul 2010

I am not sure what you are doing, however it looks as if you need a Database, rather than a spreadsheet.
Terry

VoG II 10:26 29 Jul 2010

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste into the white space on the righty

Function initials(r As Range) As String
Application.Volatile
Dim i As Long, x As Variant
x = Split(r.Value)
For i = LBound(x) To UBound(x)
initials = initials & Left(x(i), 1)
Next i
End Function

Press ALT + Q to close the code window. Now you can use

=initials(A1)

to extract the initials from A1.

Chas49 15:10 29 Jul 2010

works perfectly.

Suggest that you enter =initials(A1)in the cell to the right of that containing your names. If the first cell containing your original name is in, say, the second row and the second column then alter =initials(A1) to =Initials(B2). Then highlight that cell, copy, highlight the rest of that column (down to the last name you have) and paste. It works on 1,2,3, or 4 names - perhaps more. A good bit of code - wish I had the ability to write that way (-))

jaraba 17:01 29 Jul 2010

Works perfectly and I'm sorry there is a but.
But I need a fullstop between each initial for the mail merge. Please.

VoG II 17:03 29 Jul 2010

Maybe

Function initials(r As Range) As String
Application.Volatile
Dim i As Long, x As Variant
x = Split(r.Value)
For i = LBound(x) To UBound(x)
initials = initials & Left(x(i), 1) & "."
Next i
End Function

jaraba 22:26 29 Jul 2010

Will I have to add the vba code to every spreadsheet I may want to do the same thing ?

jaraba 01:59 30 Jul 2010

Many many thanks for all your help.
Have now saved the code to a txt file & added it to a couple of spreadsheets & it works perfectly extracting all initials & adding fullstops

Again many thanks.

chenhongjuan 02:03 30 Jul 2010

Function initials(r As Range) As String
Application.Volatile
Dim i As Long, x As Variant
x = Split(r.Value)
For i = LBound(x) To UBound(x)
initials = initials & Left(x(i), 1) & "."
Next i
End Function