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

**emory** · 07-01-2010, 01:25 AM

Thanks everyone. It looks like martindwilson's formula works best for my purposes.

Replacing Words in Respective Rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Replacing Words in Respective Rows

Re: Replacing Words in Respective Rows

Re: Replacing Words in Respective Rows

Re: Replacing Words in Respective Rows

Re: Replacing Words in Respective Rows

Re: Replacing Words in Respective Rows

Re: Replacing Words in Respective Rows

Re: Replacing Words in Respective Rows

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions