extracting 1st letter of each word in a string

[jajol]

sort of name initial..

thanks & regards

 

[Jon von der Heyden]

Assume name is in cell A1

=left(a1,1)

 

[jajol]

I meant extracting 1st letter of each word in a string

eg...Mr Excel as ME

thanks

 

[Andrew Poulsom]

Here is a custom function:

Function Initials(Txt As String) As String
    Dim Arr As Variant
    Dim x As Integer
    Arr = Split(Txt, " ")
    For x = LBound(Arr) To UBound(Arr)
        Initials = Initials & Left(Arr(x), 1)
    Next x
End Function

Note that it requires Excel 2000 or later because it uses the Split function.

To use it, press Alt+F11 to access the VBE. Click your workbook in the Project window and choose Insert|Module. Paste the code into the window on the right, and pres Alt+F11 again to return to your workbook. You can use the function in a cell like this:

=Initials(A1)

If A1 contains "This is a sentence" the function returns "Tias".

 

[fairwinds]

Hi,

Using ACONCAT that can be found searching this board:

Book1
	A	B	C	D
1	YOUCAN'TDOBUSINESS,SITTINGONYOURASS		YCDBSOYA
2
3
Sheet4

EDIT:

It would be easier to use Andrews UDF above if it works for you.

 

[BJungheim]

Book1
	A	B	C	D	E	F	G	H
1	ThisistheFirstSentence			TitFS
2	ThisistheSecondSentence			TitSS
3	Myfavoriteanimalisacat!			Mfaiac
4	Myreal.Excitingx-raycaneatlava!			Mr.Excel
5
6
7
8
9
10
11
12
13
14
15
16
Sheet1

I put together a simple UDF that can acheive what you ask(at least I think it's what you're asking)

Function Initials(Target As String) As String
    Dim x As Integer
    
    Target = Application.WorksheetFunction.Trim(Target)
    If Mid(Target, 1, 1)<> " " Then Initials = Mid(Target, 1, 1)
    For x = 1 To Len(Target)
        If Mid(Target, x, 1) = " " Then Initials = Initials & Mid(Target, x + 1, 1)
    Next
    
End Function

 

[Dsuperc]

EXTRACTING INITIALS

How could I get this to work on Excel 97?

 

[Andrew Poulsom]

You can use this custom function:

http://www.dicks-blog.com/archives/2004/05/20/split97/

 

[hedonist]

Hi all!

I hope someone can help me... I've been using the initials function as given by Andrew for quite some time now with no hassles at all. But when I sent it to someone else (with same version: Office XP) it doesn't want to work. It bombs out at the "Left" function. I then tried Jungheim's solution with the same result. This time only at the "mid" function.

Is there a setting somewhere in excel i'm missing???

 

[Andrew Poulsom]

Sounds like their installation of VBA is incomplete.