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

Worked on test spreadsheet.

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
click here

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?