Acronym from Character String?

[ajeffrey]

Can anyone point me to a macro or function that will delete all lowe
case letters, non-alpha characters (e.g. '&') and spaces in a characte
string? I have a list of journal titles (e.g. Journal of Applie
Psychology) and would like a macro to convert this to JAP.

I've seen a function for creating initials based on a string, but i
assumes only two words (whereas my strings contain between 2 and
words). Also, case sensitivity is important because I don't want mino
words ('of', 'the' etc) included in the acronym.

Thanks in advanc

 

[Harlan Grove]

Can anyone point me to a macro or function that will delete all lower
case letters, non-alpha characters (e.g. '&') and spaces in a character
string? I have a list of journal titles (e.g. Journal of Applied
Psychology) and would like a macro to convert this to JAP.

I've seen a function for creating initials based on a string, but it
assumes only two words (whereas my strings contain between 2 and 6
words). Also, case sensitivity is important because I don't want minor
words ('of', 'the' etc) included in the acronym.

See the Subst function in

http://groups.google.com/[email protected]
..com

You could use it in formulas like

=Subst("Journal of Applied Psychology","[^A-Z]","")

to remove everything except capital letters.

 

[ajeffrey]

Thanks Harlan - exactly what I was looking for. I was wasting time using
CODE to convert letters to ANSI numbers, IF to select numbers for
uppercase letters, CHAR to get back to letters... wasn't going
anywhere!

Thanks also to David McRitchie whose website
<http://www.mvps.org/dmcritchie/excel/getstarted.htm> showed a newbie
to UDFs what to do with the code to make it work in my spreadsheet.