+ Reply to Thread
Results 1 to 8 of 8

Replacing Words in Respective Rows

  1. #1
    Registered User
    Join Date
    06-27-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Unhappy Replacing Words in Respective Rows

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Replacing Words in Respective Rows

    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)

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Replacing Words in Respective Rows

    cant you just say in d2
    =PROPER(A2)&" Widgets - See Widgets in "&PROPER(A2)&","& PROPER(B2)
    or am i missing something?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Replacing Words in Respective Rows

    "Preps" and such should stay lower case

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Replacing Words in Respective Rows

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

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Replacing Words in Respective Rows

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

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

  7. #7
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Replacing Words in Respective Rows

    or maybe loop
    something like this for each row and column
    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  8. #8
    Registered User
    Join Date
    06-27-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Replacing Words in Respective Rows

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1