Remove prepositions in text using named range list

**Northstjarna** · 05-09-2013, 07:39 AM

have created a sheet to paste in text and output lower, UPPER, and Proper text I have a named range of prepositions and articles capitalizerd.

I am trying to use the SUBSTITUTE formula to replace all the occurrences or words that appear in the prepositions list

so instead of

Our Breakfast Is Consistent

We get

Our Breakfast is Consistent

so far I have this formula, but it does not work

=SUBSTITUTE(D6, TRIM(prepositions), TRIM(LOWER(prepositions))

*ignore the TRIM, Later I added spaces around the named range words, so that BUT STOPPING did not become "But StoPPING to"

I want to use proper + substitute prepositions and articles to lower text.
The reason for all this is that we have to often flow in lots of text from a csv into Adobe InDesign. Tickets and Menus.
Sometimes a client will send TEXT IN ONE FORMAT and then In Camel Case in another for the same brand and release.
Consistency is key and Excel often helps us achieve this much quicker. I could do this in PHP pre_replace but I am trying to do this in Excel and pass round the office.

Seems to me I might need some kind of split word and Vlookup combination? Not necessarily and INDEX/MATCH.

Can anyone help. Would be very appreciated. Any advice would be gratefully received.text_replacement.xlsx

Thanks

Andi

Please see attached sheet

**popipipo** · 05-09-2013, 08:31 AM

Please Login or Register  to view this content.

Is this what you want?

**Northstjarna** · 05-10-2013, 03:21 AM

hi There,

No sorry, great for Capatalising the first letter. (Did you see the attachment?)
I want to make all the prepositions lowercase, articles etc
Nouns, Adverbs and Adjectives (everything else) Capatailised.

IE
EVERY GOOD BOY DESERVES TO HAVE SOME FUN AT THE PARK - all upper
Every Good Boy Deserves To Have Some Fun At The Park - Proper (title case)
Every Good Boy Deserves to Have Some Fun at the Park - This is what I am after.

Thanks

Andi

**:) Sixthsense :)** · 05-10-2013, 03:37 AM

You can't use OLD TEXT and NEW TEXT arguments of SUBSTITUTE to MULTIPLE CONDITIONS or CELL REFERENCES.

**Northstjarna** · 05-10-2013, 04:21 AM

hi there,

Yes I know. I was thinking of using the named range as some kind of Array.
Split the string input into separate words into another array like PHP expload and compare each word to the named range list

thanks

Andi