Excel 2008 : Formula to remove consecutive capital letters from text strings

Hi JesseG123 and welcome to the forum,

Does the Mac Excel have the Proper Function? This would do what you want.
http://spreadsheets.about.com/od/exc...124_proper.htm

Wow, that was a quick response.

The version for Mac does have the Proper function, but how can I use this to delete characters? I don't just need to turn some uppercase letters into lowercase letters. I need to remove only those characters that are uppercase letters which are immediately adjacent to another uppercase letter.

E.g., I want to go from "New Jersey NJXYZ" to "New Jersey". Proper on Mac would just output "New Jersey Njxyz".

Thanks!!

Hi JesseG123,

Find your answer in the attached. It took a few helper columns to accomplish this.
I broke up your words by spaces and then checked for a vowel in each word. If no vowel was there I tossed out and reassembled the words again. See the attached and look at the formulas and see if it works for you.

BTW - I only did a 5 possible word string in Col A.

@Cutter,

The job was impossible for all possible words. I only left words that had vowels in them. You are correct that St Louis would drop the St.
Logic is a funny thing. It works for some problems and not others. Euclidian Geometry works on earth as long as it isn't too much earth. This problem needed spaces between words and a vowel in each name to survive my code.

I just guessed/assumed that the OP had city names that all had vowels in them.

There would be other problems if there weren't spaces between the separate words. In the example "AlbanyXYZ" wouldn't be converted correctly as there is no space. I assumed s/he typed it incorrectly and there really was a space between the y and X.

The initial formula is a really good start (and very smart), but it turns out there are actually 3 issues:

1. As Cutter notes, in examples like "St Louis", the "St" is removed (and I need it not to be removed).

2. As MarvinP notes, examples like "AlbanyXYZ" are problematic because they don't get separated into two words.

3. I'm supposed to use this to eliminate consecutive uppercase letters in text strings that also contain characters from other languages (e.g. Chinese, Russian, Arabic, etc.). The characters that need to be removed will always be English.

So I really need to pick out only and all of those characters that are
(A) English,
(B) uppercase, and
(C) right next to at least one other uppercase English character.

Any ideas?

Thanks so much!

Hey JesseG123,

You have Mac 2008 Excel which doesn't have VBA included. Excel doesn't know the difference between capital and lower case letters without going to VBA.

To include the "St" you could add an "IFERROR(FIND("St",B2),FALSE) in the formula in G2.
You should also include "y" as a vowel for cities like Lynn and "Ft" for the abreviation of "Fort".

I have no answer for the AlbanyXYZ if there isn't a space in there.

Hi JesseG,

If you had VBA behind Excel we might write a VBA Macro that did what you needed. I'd say you would need more than 5000 rows that needed "fixing" before the time would be worth it.

With VBA a simple UDF should work:

Please Login or Register  to view this content.

Thanks for your help!! I really appreciate it.
Not to push my luck... but if either of you have any bright ideas about how to make this work across languages with different characters (e.g. Chinese, Arabic, Greek, etc.), let me know!
You guys rock!

PS. Re: MarvinP, definitely worth it -- I need to fill out about 40,000 rows for at least 29 different languages, for a total of something like 1,160,000 rows total.

Perhaps it's time to upgrade to Excel 2011 for the Mac. It has VBA abilities.