Remove letters from ordinal numbers within a string??

[hardog]

hi,

does anyone have a vba function to remove the letters from ordinal numbers within a string i.e.

'92nd Street' becomes '92 Street', and
'West 21st St' becomes 'West 21 St'

cheers,
harry

 

[mdlueck]

remove the letters from ordinal numbers

Hua????

http://en.wikipedia.org/wiki/Ordinal_number
http://en.wikipedia.org/wiki/Ordinal_date

 

[plog]

Hua????

http://en.wikipedia.org/wiki/Ordinal_number
http://en.wikipedia.org/wiki/Ordinal_date

You were so close to finding out what he meant (and he used the correct term by the way). First link in the Ordinal Number page you linked to went to this: http://en.wikipedia.org/wiki/Ordinal_number_(linguistics))


For hardog--I don't know of any built-in function, so you will probably have to roll your own. My suggestion would be to build a function that takes a string that then searches that string for ordinal number markers (i.e. 'st', 'nd', 'rd', 'th') and then looks at the character prior to them to see if its a number. You'll need the InStr function (http://www.techonthenet.com/access/functions/string/instr.php) and the Mid function (http://www.techonthenet.com/access/functions/string/mid.php).

 

[hardog]

cheers plog - its a tricky one but ill have a go

 

[mdlueck]

You were so close to finding out what he meant

Foiled by the overlooked third link!

 

[elliotgr]

Or look for numbers (using a CHR range) in the string and delete the following two characters if there are any?

 

[timocoder]

In case this helps anyone else (I stumbled across this when trying solve a very similar scenario).. Here's some code I wrote that does the same thing, not 100% tested across every conceivable case so I've added a 'limiter' to prevent any strange infinite loops which can probably be removed once it's more thoroughly tested:

Function removeOrdinals(strIn As String) As String
'removeOrdinals("test 1st 3rd street")
'returns:  "test 1 3 street"
Dim i As Long
Dim limiter As Long
Dim prev As String
Dim bufReturn As String
Dim ordinals(1 To 4) As String
ordinals(1) = "st": ordinals(2) = "nd": ordinals(3) = "rd": ordinals(4) = "th"
bufReturn = strIn
For Each o In ordinals
i = 0
Do
 i = InStr(i + 1, bufReturn, o, vbTextCompare)
 limiter = limiter + 1
 If i > 1 Then
  prev = Mid(bufReturn, i - 1, 1)
  If IsNumeric(prev) Then
    bufReturn = Left(bufReturn, i - 1) & Right(bufReturn, Len(bufReturn) - (i + 1))
  End If
 End If
Loop Until i = 0 Or limiter > 100
Next

removeOrdinals = bufReturn
End Function

 

[arnelgp]

the Regular expression is much simpler:

' arnelgp
Public Function udfRemoveOrdinalNumber2(ByVal p As Variant) As Variant
    Const sPattern As String = "([0-9]{1,})(st|nd|rd|th)"
    If Trim(p & "") = "" Then Exit Function
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = sPattern
        p = .Replace(p, "")
    End With
    While InStr(p, "  ")
        p = Replace(p, "  ", " ")
    Wend
    udfRemoveOrdinalNumber2 = p
End Function

 

[timocoder]

the Regular expression is much simpler:

' arnelgp
Public Function udfRemoveOrdinalNumber2(ByVal p As Variant) As Variant
    Const sPattern As String = "([0-9]{1,})(st|nd|rd|th)"
    If Trim(p & "") = "" Then Exit Function
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = sPattern
        p = .Replace(p, "")
    End With
    While InStr(p, "  ")
        p = Replace(p, "  ", " ")
    Wend
    udfRemoveOrdinalNumber2 = p
End Function

So two things - 1) I didn't even know we could use Regexp this easily. Very awesome. Thanks for sharing!
2) The function you shared doesn't quite render the result this post was going for:

debug.Print udfRemoveOrdinalNumber2("test of the 4th place on the 3rd spot")
Expected output -> test of the 4 place on the 3 spot
Actual output -> test of the place on the spot

 

[deletedT]

debug.Print udfRemoveOrdinalNumber2("test of the 4th place on the 3rd spot")
Expected output -> test of the 4 place on the 3 spot
Actual output -> test of the place on the spot

Change replace as following

p = .Replace(p, "$1")