Replacing Words in Respective Rows

**emory** · 06-29-2010, 12:55 AM

Hi, I am trying to replace each occurence of these items:

{city_name}, {state_name}, {state_abbreviation}

with its corresponding city, state and state abbreviation in these columns (the first three):

city_name state_name state_abreviation

So, for example, column D, Row 2 would read: "Atlanta Widgets - See Widgets in Atlanta, Georgia"

All cities and states need to be in title case except the slug needs to be in lowercase.

The attached file is only a sample of the thousands of rows.

I hope this is clear. Thanks in advance.

**davesexcel** · 06-29-2010, 07:14 AM

The UDF here
http://www.duehren.com/CodeSamples.html
Attempts to do what you want.
Copy and paste it into a worksheet module
then use the formula
=propercase(A1)

**martindwilson** · 06-29-2010, 07:31 AM

cant you just say in d2
=PROPER(A2)&" Widgets - See Widgets in "&PROPER(A2)&","& PROPER(B2)
or am i missing something?

**davesexcel** · 06-29-2010, 07:35 AM

"Preps" and such should stay lower case

**martindwilson** · 06-29-2010, 07:48 AM

???????????? preps ,slug whats that?

**davesexcel** · 06-29-2010, 07:51 AM

Really?
The Rain In Spain.
The Rain in Spain.

I never heard the term Slugs, may be a U.S. thing,LOL

**pike** · 06-29-2010, 07:57 AM

or maybe loop
something like this for each row and column

.Replace What:="city_name", Replacement:=Range("a3"), LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False