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

 

[gaz_chops]

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!

I think it will work in most cases, it searches for "Th " then replaces it with "th ", so only a problem if you have something else that ends in "Th "

 

[Oaktree]

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!

Yeah, ignore that. I overthought it and reversed the capitalization. Can't think of a situation where you'd have capital T then lower h than space if it's not an ordinal suffix, but you might still be interested in considering Rick's suggestion for learning purposes.

 

[mintz]

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

The code works great, first time I use VBA code so I followed your instructions and it works like magic! I'll use Oak's just for convenience but I've saved the code and will definitely use it on different occasions!

Yeah, ignore that. I overthought it and reversed the capitalization. Can't think of a situation where you'd have capital T then lower h than space if it's not an ordinal suffix, but you might still be interested in considering Rick's suggestion for learning purposes.

Indeed the first formula does it all, I won't look further!

Thank you so much!