In excel, how do we pull the first letter from words in a sentence

  • Thread starter Thread starter Deepak
  • Start date Start date
Not sure exactly about your question, but in Excel with "Left" command you
can choose any number of characters from beginning of a sentence.
With combination of "Left" and "Middle" and the space between words in a
sentence you can pull out the first char. of words in a sentence.
 
Deepak said:
I need to assign codes to words for e.g.
Product Master would need a code like "PM"
Click to expand...

One way is to install & use the UDF FrstLtrs below by Ron Rosenfeld ..
(UDF = user defined function)

To install the UDF:
Press Alt+F11 to go to VBE
Copy n paste the UDF into the code window (whitespace on right)
Press Alt+Q to get back to Excel

In Excel,
Assuming your data in A1 down, eg: Product Master,
put in B1: =frstltrs(A1)
which returns: PM
Copy B1 down to extract the desired results

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

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
 
Deepakji ,

Suppose your data is in A1 cell then put following formula in cell B1,

=MID(A1,1,1)&MID(A1,FIND(" ",A1)+1,1)

Regards

H S Shastri

=====================================================
 
This will return only first 2 letters. Amendment is necessary to work for
more than 2 words.
 
Missing line earlier, to insert module:
...
Press Alt+F11 to go to VBE
Click Insert > Module
Copy n paste the UDF into the code window (whitespace on right)

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
 
Dear Ron

I think it was you who put me on to Longre's moregfunc in the first place,
and your reference to it on this thread is the most recent on this forum, so
I hoped that here would be an appropriate place to put my question. I think
Longre's MCONCAT is great and it is the centre of a major spreadsheet I am
working on.

Overnight (literally last night Western Australian time - Tue 3rd Feb 2008)
- my Excel installation has ceased to allow it be part of it. I have
uninstalled and reinstalled morefunc. I have rebooted. I have downloaded a
new copyfrom CNet. All to no avail.

Unfortunately all I get, when trying to use the home site for morefunc, is
lots of errors.

If you have any ideas for what might be going on, or what settings I could
check on Windows / Excel / Norton, I would be most grateful.

Best regards

Philip Hunt
Medina, Kwinana
Perth, Western Australia
 
Back
Top