Extracting text

[Treganna]

I need to extract initials from a column of names to form a column of initials e.g. Joe to J, Joe David to J D, Joe David George to J D G, etc. Any ideas please.

 

[Scott Huish]

Hi, welcome to the board!

Try the following UDF:

Function Initials(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Z]"
    .Global = True
    Initials = .Replace(r, "")
End With
End Function

Excel Workbook
	A	B
1	Joe Smith	JS
2	Joe David George	JDG
Sheet1

 

[Treganna]

Thanks for the info. I'm a bit of a novice but I'll give it a go.

 

[Scott Huish]

Do you know how to put in the code?

 

[Treganna]

I have used VBA once with Macros but I'm not sure how to input the code (but eager to learn!).

 

[Treganna]

Success! Thanks for your help. Could I add a space between the initials?

 

[Scott Huish]

Function Initials(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Z ]"
    .Global = True
    Initials = .Replace(r, "")
End With
End Function