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

[mintz]

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?

 

[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.

Try: =REPLACE(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9}&"Th",A1&"0Th1Th2Th3Th4Th5Th6Th7Th8Th9Th"))+1,2,"th") instead.

 

[mintz]

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

 

[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?

 

[gaz_chops]

Try the 1st formula offered

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

 

[mintz]

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!

 

[Oaktree]

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.

 

[mintz]

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!

 

[billyheanue]

Hey Mintz,

Try =15

That should help

 

[Rick Rothstein]

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

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.