Extract First Letter from each word in a Text String
 

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
--
Casey

Extract First Letter from each word in a Text String
 

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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

"Casey" wrote:

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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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.
--
Casey




"Ronio" wrote:

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

"Casey" wrote:

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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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




"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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




"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

On Fri, 5 Jun 2009 10:13:01 -0700, Casey
wrote:

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

Extract First Letter from each word in a Text String
 

On Fri, 5 Jun 2009 10:13:01 -0700, Casey
wrote:

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

Extract First Letter from each word in a Text String
 

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.

--
Rick (MVP - Excel)


"Casey" wrote in message
...
I inserted a new Module and placed the code in that Module.
--
Casey




"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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.

--
Rick (MVP - Excel)


"Casey" wrote in message
...
I inserted a new Module and placed the code in that Module.
--
Casey




"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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.
--
Casey




"Casey" wrote:

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




"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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.
--
Casey




"Casey" wrote:

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




"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey

Extract First Letter from each word in a Text String
 

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).

Casey wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey









--

Dave Peterson

Extract First Letter from each word in a Text String
 

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).

Casey wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Casey" wrote in message
...
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




"Rick Rothstein" wrote:

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)


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




"Rick Rothstein" wrote:

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 (MVP - Excel)


"Casey" wrote in message
...
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
--
Casey









--

Dave Peterson