Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

  1. #1
    Board Regular
    Join Date
    Aug 2015
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    I have this sample string for a street address:

    39Th Ann Thomas St
    Ann Thomas 39Th St
    Ann Thomas St 39Th

    How to convert the 39Th to lower-case (th) but keep the Thomas in PROPER-case? (Th)

    The __th position is dynamic, could be anywhere in the string, in the beginning or middle or end

    I want to use SUBSTITUTE(A1,"XTh","Xth") where X is any digit/number - what do I need to write instead of the X so that the SUBSTITUTE replaces "Th" with "th" only when it comes after a digit/number?
    Last edited by mintz; Aug 5th, 2015 at 06:37 PM.

  2. #2
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,913
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Welcome to the board!

    Instead of trying to trigger of the numbers, what if you only considered instances where the "th" is either followed by a space or is the last two characters in the string?

    Try: =TRIM(SUBSTITUTE(A1&" ","Th ","th "))

    Edit: Meh, that's sloppy for other addresses.

    Try: =REPLACE(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9}&"Th",A1&"0Th1Th2Th3Th4Th5Th6Th7Th8Th9Th"))+1,2,"th") instead.
    Last edited by Oaktree; Aug 5th, 2015 at 06:52 PM.
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  3. #3
    Board Regular
    Join Date
    Aug 2015
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Quote Originally Posted by Oaktree View Post
    Welcome to the board!

    Instead of trying to trigger of the numbers, what if you only considered instances where the "th" is either followed by a space or is the last two characters in the string?

    Try: =TRIM(SUBSTITUTE(A1&" ","Th ","th "))

    Edit: Meh, that's sloppy for other addresses.

    Try: =REPLACE(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9}&"Th",A1&"0Th1Th2Th3Th4Th5Th6Th7Th8Th9Th"))+1,2,"th") instead.
    I'm getting so close!!! I used this formula and it works perfect but what if I have multiple instances of Xth in the same string? Like 39Th Ann Thomas St, 5Th Floor

  4. #4
    Board Regular
    Join Date
    Aug 2015
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Quote Originally Posted by mintz View Post
    I'm getting so close!!! I used this formula and it works perfect but what if I have multiple instances of Xth in the same string? Like 39Th Ann Thomas St, 5Th Floor
    Hi just a quick followup I tried to improve the formula so it works with multiple instances of XTh in a string but couldn't get it to work

    It works perfectly with one instance of __Th but in case of multiple instances, like "39Th Ann Thomas St, 5Th Floor" it does change the 39Th but ignores the 5th

    How do I go from here?

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,852
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Try the 1st formula offered

    =TRIM(SUBSTITUTE(A1&" ","Th ","th "))
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  6. #6
    Board Regular
    Join Date
    Aug 2015
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Quote Originally Posted by gaz_chops View Post
    Try the 1st formula offered

    =TRIM(SUBSTITUTE(A1&" ","Th ","th "))
    Beautiful!!! It works!! I didn't even give it a try since Oaktree said it is sloppy for some addresses then I just tried the 2nd but now applied the 1st formula and it works perfectly!
    I mixed it in a master formula and tested it on a few addresses, it all works!
    Thank you guys so much!

  7. #7
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,913
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Glad it's working for you.

    It'll be fine in the example you posted, but note the limitation of =TRIM(SUBSTITUTE(A1&" ","Th ","th ")) if you have Earth St. That formula will change it to EarTh St, hence why I said it's a little sloppy.
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  8. #8
    Board Regular
    Join Date
    Aug 2015
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Quote Originally Posted by Oaktree View Post
    Glad it's working for you.

    It'll be fine in the example you posted, but note the limitation of =TRIM(SUBSTITUTE(A1&" ","Th ","th ")) if you have Earth St. That formula will change it to EarTh St, hence why I said it's a little sloppy.
    I just tested it with "Earth St." it still works! Do you have a few more examples you think will be sloppy with the formula? I'll try them and see what happens!

  9. #9
    Board Regular
    Join Date
    Jul 2015
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Hey Mintz,

    Try =15

    That should help

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

    Can you make use of VB code? If so, give this UDF (user defined function) as try...

    Code:
    Function ProperProper(S As String) As String
      ProperProper = StrConv(S, vbProperCase)
    End Function

    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ProperProper just like it was a built-in Excel function. For example,

    =ProperProper(A1)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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