Extract First Letter from each word in a Text String

[Casey]

I have a single cell formatted as text into which a variety of information
might be entered. What I want to do is extract the first letter of each word
into another cell.

Examples:
A1 A2
Basalt Regional Library BRL
Carbondale Rural Fire Protection District CRFPD
Williams Residence WR

 

[Rick Rothstein]

This macro will process all selected cells and put the abbreviations into
the next column...

Sub GetFirstLetters()
Dim X As Long
Dim C As Range
Dim S As String
Dim Words() As String
For Each C In Selection
Words = Split(C.Value)
S = ""
For X = 0 To UBound(Words)
S = S & Left(Words(X), 1)
Next
C.Offset(0, 1).Value = S
Next
End Sub

 

[Casey]

Rick,
Thank you for the reply. The procedure works exactly as needed. Could this
procedure be made into a Function?
--
Casey

This macro will process all selected cells and put the abbreviations into
the next column...

Sub GetFirstLetters()
Dim X As Long
Dim C As Range
Dim S As String
Dim Words() As String
For Each C In Selection
Words = Split(C.Value)
S = ""
For X = 0 To UBound(Words)
S = S & Left(Words(X), 1)
Next
C.Offset(0, 1).Value = S
Next
End Sub

 

[Rick Rothstein]

This should do what you want...

Function GetFirstLetters(S As String) As String
Dim X As Long
Dim Words() As String
Words = Split(S)
For X = 0 To UBound(Words)
GetFirstLetters = GetFirstLetters & Left(Words(X), 1)
Next
End Function

--
Rick (MVP - Excel)

Rick,
Thank you for the reply. The procedure works exactly as needed. Could this
procedure be made into a Function?

 

[Ronio]

Wouldn't be very pretty as a function. You would need to walk through
looking for your spaces. The following would get your first two words.

=LEFT(A1,1)&MID(A1,SEARCH(" ",A1)+1,1)

You would need to build it to search for the third word (embed another MID
that has the starting point after the first search) and so on for a fourth
and fifth word.

Ronio

Rick,
Thank you for the reply. The procedure works exactly as needed. Could this
procedure be made into a Function?

 

[Casey]

Rick,
Thank you again for the effort. But I must be using the function wrong, it
is throwing a #NAME? error. I entered the function in a blank cell pointing
to the cell with the text string, like so: =GetFirstLetters (C10).

 

[Casey]

Ronio,
Thanks for the input, but you're right, a formula solution gets pretty
complex and I'm not sure if you could cover the variables.

 

[Rick Rothstein]

Where did you put the code at? UDFs must be placed in a Module
(Insert/Module from the VB editor's menu bar).

 

[Casey]

I inserted a new Module and placed the code in that Module.

 

[Ron Rosenfeld]

I have a single cell formatted as text into which a variety of information
might be entered. What I want to do is extract the first letter of each word
into another cell.

Examples:
A1 A2
Basalt Regional Library BRL
Carbondale Rural Fire Protection District CRFPD
Williams Residence WR

Here is a UDF that will return the first letter of each word.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=FirstLtrs(A1)

in some cell.

======================
Option Explicit
Function FirstLtrs(S As String) As String
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\b\w"
If re.test(S) = True Then
Set mc = re.Execute(S)
For Each m In mc
FirstLtrs = FirstLtrs & m
Next m
End If
End Function
========================
--ron

 

[Rick Rothstein]

And you are saying the function provides a #NAME? error for you? You did
pick Module and not "Class Module", correct? If your answer is yes to both
of those, then I'm not sure what to tell you as the function works fine on
my system. You might try closing Excel down and reopening it to see if that
clears whatever is blocking the UDF from working for you.

 

[Casey]

Rick,
My apologies, I did insert a new module, but some how or other I ended up
with the function code in the sheet code. Sorry. Thank you very much, this
function will save me a great deal of time.

 

[Dave Peterson]

And then it worked ok?

Did you put the function in the same workbook as the data?

If you put the function in your personal.xls workbook so you could use it on any
workbook, then you'll have to include the workbook name:

=personal.xls!getfirstletter(c10)

If this doesn't help, make sure you didn't make any typing errors--name of the
function in the VBE or in the cell's formula.

=====
And ....

This code uses VBA's Split command. You have to be running xl2k or higher (and
it won't work on a Mac).