Extracting the First letter from each word

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Is there a formula that will give me the inituals of a name. Each name
could be 2 or three names. Eg if "Joe Blows" then JB is required, if "Blow
Me Down", then BMD is required
Rob
 
Assuming just spaces as a delimiter -- no punctuation at all:

I'd use a user defined function:

Option Explicit
Function makeAcronym(rng As Range) As String

Dim StrIn As String
Dim StrOut As String
Dim iCtr As Long

Set rng = rng(1)

StrIn = " " & Application.Trim(rng.Value)
StrOut = ""
For iCtr = 1 To Len(StrIn) - 1
If Mid(StrIn, iCtr, 1) = " " Then
StrOut = StrOut & Mid(StrIn, iCtr + 1, 1)
End If
Next iCtr

makeAcronym = StrOut

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type
=makeAcronym(a1)

in a cell (but point at the cell with the string).

===
but you could use a formula, too:

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

It appends a bunch of spaces to the end, then finds spaces and takes the next
character.
 
Thanks for that great function and formula, Dave.
Some questions though...
1. Could I just ask how to get that into the list of functions correctly.
I pasted it into a module in Personal.xls. If I want to use it in another
workbook it shows up within the full list as "PERSONAL.XLS!makeAcronym".
2. Also, can it be stored in a particular function category?
3. Can it be amended so that it doesn't show a Value error if there's not
more than one name (or even blank). Best would be if it still showed the
one initial if only one name exists and, blank if blank.
If it's not too much trouble, could you also have the formula perform the
same if only one name is used (or is blank )?
Rob
 
#1&2: If you don't want to prefix the function with =personal.xls!, you could
save your personal.xls file as an addin (personal.xla).

Then it'll be treated just like a builtin function (=makeacronym(a1)). But the
bad news is if you have macros (subs) that are called via Tools|macro|macros...,
then they won't show up anymore.

You could separate your functions into a different addin and keep the subs
separate. But I like John Walkenbach's MenuMaker program. It adds an item to
the worksheet menubar and you can get to all your macros via that.

http://j-walk.com/ss/excel/tips/tip53.htm

(I find this nicer organization than a custom toolbar when you have lots of
macros.)

Laurent Longre has a technique at John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee009.txt
(look for: POWER PROGRAMMING TECHNIQUES)

That shows you how to categorize functions (I've never used it, though).

#3. Hmmm. I couldn't get it to return an error--unless there was an error in
that cell. And you could put something in the code to trap for an error--or
just let excel return it itself.

(What did you have in that cell that returned an error. (even a empty cell or a
cell with spaces returned "").
 
Thanks Dave, I'll certainly check that MenuMaker out.
Regarding the error, it's not in the function procedure you sent, just the
formula.
I was a bit reluctant to use the function as I thought it easier to just
directly put the formula into the cells.
I think though, that if I can get the function to operate as a built in
function it would be a better option.
With the formula, it returns a #Value error if the cell is blank or has only
one word.
Rob
 
The only way I could get an error is if I
Tools|options|Transition Tab
Transition Formula Evaluation formula checked.

And I would think that for most excel users, this isn't required or
recommended. (It's only caused heartache and pain for me).

And even with that checked, a single word worked ok.

If you copy the original formula in and paste it into the formula bar (say with
B1 selected), do you get the same error? Maybe the additional spaces didn't
come through in the retyping....

Here's the same formula with dots as replacements for the spaces.

=TRIM(LEFT(TRIM(A1),1)
&MID(TRIM(A1)&"...",FIND(".",TRIM(A1)&"...")+1,1)
&MID(A1,FIND(".",TRIM(A1)&"...",FIND(".",TRIM(A1)&"...")+1)+1,1))

(Change them back to spaces or it surely won't work correctly.)
 
Well, that certainly is strange, but I'm guessing you can figure that out,
Dave.
I took up your suggestion that, "Maybe the additional spaces didn't come
through in the retyping...." and I deleted the spaces in the formula and
typed the spaces in myself. The formula LOOKS EXACTLY the same, but now it
works without the error. (I originally just copied and pasted the formula
straight from your first reply.) In other words, all I did was that where
there is one space in the formula I substituted one space and where there
are three spaces I entered 3 spaces. (AND, that was in the original formula
you sent.)
??? but thanks again.
Rob
 
Whatever the cause, glad it's working for you.


rob said:
Well, that certainly is strange, but I'm guessing you can figure that out,
Dave.
I took up your suggestion that, "Maybe the additional spaces didn't come
through in the retyping...." and I deleted the spaces in the formula and
typed the spaces in myself. The formula LOOKS EXACTLY the same, but now it
works without the error. (I originally just copied and pasted the formula
straight from your first reply.) In other words, all I did was that where
there is one space in the formula I substituted one space and where there
are three spaces I entered 3 spaces. (AND, that was in the original formula
you sent.)
??? but thanks again.
Rob
Click to expand...
 
Dave, just an addendum to this.
I was curious as to the reason why I couldn't copy and paste your formula
directly and not get it to work without retyping the spaces, so I copied the
formula to MSWord to be able to view those spaces. Strangely, some of the
spaces are not the normal spaces at all.
To replicate those special spaces can be done by holding down the ctrl
+shift keys when hitting the space bar in MSWord. (Apparently called a
non-breaking space, whatever they do??) I wonder, did you copy the formula
from somewhere else or write it in another program other than Excel?
Have a great new year!
Rob
 
I don't use OE to access the newsgroup--but tons of people do. I wonder if
they've ever had a similar problem.

(I use Netscape 4.7x and I get the "regular" spaces when I copy|paste.)

rob said:
Dave, just an addendum to this.
I was curious as to the reason why I couldn't copy and paste your formula
directly and not get it to work without retyping the spaces, so I copied the
formula to MSWord to be able to view those spaces. Strangely, some of the
spaces are not the normal spaces at all.
To replicate those special spaces can be done by holding down the ctrl
+shift keys when hitting the space bar in MSWord. (Apparently called a
non-breaking space, whatever they do??) I wonder, did you copy the formula
from somewhere else or write it in another program other than Excel?
Have a great new year!
Rob
Click to expand...
 
I don't have Netscape so can't check that theory out. Anyway, just an
observation and something to look at next time I copy a formula from the
newsgroup. Maybe copying first to Word to edit it may be a reasonable thing
to do in future as formatting certainly is more obvious in that program.
Rob
 
When I copy and paste from Netscape into Word, it shows a paragraph mark
where you started a new line in the formula.

From OE, it shows a manual line break, and non-breaking space.

Another reason to use Netscape!

Dave said:
I don't use OE to access the newsgroup--but tons of people do. I wonder if
they've ever had a similar problem.

(I use Netscape 4.7x and I get the "regular" spaces when I copy|paste.)
Click to expand...
 
Back
Top