Extracting 1st letter of each word in a string

[jmp2004]

Hi, I want to extract the 1st letter of each word in a string. So fo
example, in the string "my name is fred" I want to extract "mnif"

the cell containing the text could have more than four words, however
am only interested in the first four words that have been entered.

any suggestions would be great

Thank

 

[shternm]

You can you use left formula to get the first letters and concatenat
the results.
For example:
=left(a1,1) & left(b1,1)

a1=My
b1=Name

The result will be M

 

[Frank Kabel]

Hi
and are there always at least for words?

 

[jmp2004]

Hi thanks for the tip.

However all the text is in one cell eg A1 contains "My Name is Fred

 

[jmp2004]

Hi, unfortunately no the number of words in the cell could be from 1 t
4 or more than 4

 

[Gary Brown]

It's ugly, but this will work for up to 4 words.

=LEFT(A1,1)&IF(ISERROR(MID(A1,FIND("~",SUBSTITUTE
(A1," ","~",1))+1,1)),"",MID(A1,FIND("~",SUBSTITUTE
(A1," ","~",1))+1,1))&IF(ISERROR(MID(A1,FIND("~",SUBSTITUTE
(A1," ","~",2))+1,1)),"",MID(A1,FIND("~",SUBSTITUTE
(A1," ","~",2))+1,1))&IF(ISERROR(MID(A1,FIND("~",SUBSTITUTE
(A1," ","~",3))+1,1)),"",MID(A1,FIND("~",SUBSTITUTE
(A1," ","~",3))+1,1))

HTH,
Gary Brown

 

[Ron Rosenfeld]

Hi, I want to extract the 1st letter of each word in a string. So for
example, in the string "my name is fred" I want to extract "mnif"

the cell containing the text could have more than four words, however I
am only interested in the first four words that have been entered.

any suggestions would be great

Thanks

It's easy with a User Defined Function.

<alt><F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window.

To use this UDF, enter a formula of the type

=FrstLtrs(A1) where your string is in A1.

===================

--ron

 

[icestationzbra]

this is a rather bulky algorithm. not too pretty, but it works.

select a column of cells that have these strings that you want t
extract from, then run this code. currently, it is programmed to spi
out the extract in the 26th column. you can change it to whicheve
column you want the output at. look for 26 and change it to anothe
number (26 corresponds to column Z).

do let me know if there are bugs in it, i will be glad to know.



Option Explicit

Sub ExtractFirstLetterFromFourWords()

Dim sIn As String, s1 As String, s2 As String, s3 As String, s4 A
String, sOut As String, sTemp As String
Dim i As Integer, n As Integer, x As Integer
Dim rngCell As Range, rngAll As Range

Set rngAll = Selection

For Each rngCell In rngAll

s1 = ""
s2 = ""
s3 = ""
s4 = ""

sIn = rngCell.Value

If sIn = "" Then Exit Sub

sTemp = Application.WorksheetFunction.Substitute(sIn, " ", "")

x = Len(sIn) - Len(sTemp)

If x < 1 Then

s1 = Left(sIn, 1)
GoTo ExitPoint

End If

i = Application.WorksheetFunction.Find(" ", sIn)
n = Len(sIn) - i

s1 = Left(Left(sIn, i), 1)

If x < 1 Then GoTo ExitPoint

sTemp = Mid(sIn, i + 1, Len(sIn))

s2 = Left(Left(sTemp, i), 1)

If x < 2 Then GoTo ExitPoint

i = Application.WorksheetFunction.Find(" ", sTemp)
n = Len(sTemp) - i

sTemp = Mid(sTemp, i + 1, Len(sIn))

s3 = Left(Left(sTemp, i), 1)

If x < 3 Then GoTo ExitPoint

i = Application.WorksheetFunction.Find(" ", sTemp)
n = Len(sTemp) - i

sTemp = Mid(sTemp, i + 1, Len(sIn))

s4 = Left(Left(sTemp, i), 1)

ExitPoint:

'MsgBox s1 & s2 & s3 & s4

sOut = s1 & s2 & s3 & s4

rngCell.Offset(0, 26).Value = sOut

Next rngCell

End Su

 

[Ron Rosenfeld]

Hi, I want to extract the 1st letter of each word in a string. So for
example, in the string "my name is fred" I want to extract "mnif"

the cell containing the text could have more than four words, however I
am only interested in the first four words that have been entered.

any suggestions would be great

Thanks

It's easy with a User Defined Function.

<alt><F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window.

To use this UDF, enter a formula of the type

=FrstLtrs(A1) where your string is in A1.

======================
Function FrstLtrs(str As String) As String
Dim temp
Dim i As Long

temp = Split(Trim(str))

For i = 0 To UBound(temp)
FrstLtrs = FrstLtrs & Left(temp(i), 1)
Next i

End Function
================================
===================

--ron

 

[jmp2004]

Hi Gary,

Thanks for your formula. However, when I paste this into the cell an
press enter I get a message alert box saying there is an error. But i
is not obvious where the error is. I am using Excel version 2002.

Any ideas

 

[jmp2004]

Hi Ron,

There was no code in your message.

 

[jmp2004]

thanks Ron


You have saved me loads of time. I have to produce course codes fo
our learning management system at work for over 500 courses and th
course code is made up of a number of letters and numbers and include
the first letters of the course name.

You are a star!

thanks again

Jame

 

[icestationzbra]

ron,

that was great a great piece of code. thank you.

mac

 

[Don Guillett]

A good one!!!

 

[jmp2004]

following on... as I only wanted four characters to show in the new cel
I have used the =LEFT(cell reference,4) function and combined this i
the concatenate function =CONCATENATE(L2,K2,"-",LEFT(M2,4),D2) to sho
my course I

 

[Gary Brown]

On the forum, the formula gets separated into 7 separate
lines. Copy the formula into a cell then go to the end of
each line and hit the delete button. That will get rid of
the hidden line-breaks. When the formula is 'whole'
again, just hit the enter key and it should work. Of
Course, you're going to have to change the 'A1' reference
to whatever cell you want to reference.
Good luck,
HTH,
Gary Brown

 

[Ron Rosenfeld]

thanks Ron


You have saved me loads of time. I have to produce course codes for
our learning management system at work for over 500 courses and the
course code is made up of a number of letters and numbers and includes
the first letters of the course name.

You are a star!

thanks again

James

You're welcome. Thank you for the feedback.

--ron
--ron

 

[Ron Rosenfeld]

A good one!!!

I get inspired on rare occasions. Split and Join are in my head these days for
something I'm working on.


--ron

 

[Ron Rosenfeld]

following on... as I only wanted four characters to show in the new cell
I have used the =LEFT(cell reference,4) function and combined this in
the concatenate function =CONCATENATE(L2,K2,"-",LEFT(M2,4),D2) to show
my course ID

Well, I had overlooked that.

It is easy to limit the length to four characters in the UDF:

===========================

Function FrstLtrs(str As String) As String
Dim temp
Dim i As Long

temp = Split(Trim(str))

For i = 0 To Application.WorksheetFunction.Min(3, UBound(temp))
FrstLtrs = FrstLtrs & Left(temp(i), 1)
Next i

End Function

===========================


--ron