Thanks:  0
Likes:  0

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

1. ## "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?

2. ## 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.

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

Originally Posted by Oaktree
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.

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. ## Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

Originally Posted by mintz
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. ## Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

Try the 1st formula offered

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

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

Originally Posted by gaz_chops
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. ## Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

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.

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

Originally Posted by Oaktree

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. ## Re: "39Th Ann Thomas St" How to make it 39th but keep the Thomas?

Hey Mintz,

Try =15

That should help

10. ## 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.

## User Tag List

#### Posting Permissions

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