Formatting: Address Standardization - Formatting Address num

[sbgis63]

I work as a GIS Administrator for a law enforcement agency. To assist the various divisions, i geocode (address match) various address databases to a street network which contains address range information.

The problem is that the databases containing Street names that are numbers are just that (i.e., 1, 2, 33, etc). I need to format them so they are in the following format: 1st, 2nd, 3rd. By the way they parse the addresses like so:

Prefix Street_no Streetname Street_type
SW 1230 1 Ave

The streetname 1 should be 1st not 1



Is there a solution other than search and replace every single number type. What i do know is that the address name that are numbers only go up to 300. Thus, width is three digit number the ideal situation would be to create some type of logical statement where ?0 equals ?0th this would replace all numbers such as 10th, 20th, 30th...90th"

I am more GIS savvy than excel but i am willing to learn if there are any solutions to this problem. I will continue to go through the archive to see if this has already been addressed (no pun intended).

But any advice would be great

 

[Jack in the UK]

OK this is going to be tough, law man eh! OK so no point asking to much i know this from my agency days "county courts"

now run with me assume you had a date, which in the 1 2 3 or to 31 need the correct end i.e. st nd rd th now then if so the street is it need to be the day so I can force excel to go xxxxxxx 1st xxx

if so can I ask silly questions, US say zip? I don’t read them or understand them, need some help, who are they made up.. do they follow pattern.

Please give few examples, I have a plan..

I’m not promising thou:

 

[Al Chara]

Try the following code:

Sub FormatNumbers()
Dim SecondText As Variant
Dim OriginalText As String, FirstText As String, ThirdText As String
Dim PrevSpace As Integer, NextSpace As Integer, LastSpace As Integer

For Each cell In Selection
OriginalText = cell.Value & " "
PrevSpace = 0
NextSpace = 0
LastSpace = 0

Do
PrevSpace = Application.WorksheetFunction.Search(" ", OriginalText, PrevSpace + 1)
NextSpace = Application.WorksheetFunction.Search(" ", OriginalText, PrevSpace + 1)
LastSpace = Application.WorksheetFunction.Search(" ", OriginalText, NextSpace + 1)
Loop Until LastSpace = Len(OriginalText)
OriginalText = cell.Value
FirstText = Left(OriginalText, PrevSpace)
ThirdText = Right(OriginalText, Len(OriginalText) - NextSpace)
SecondText = Mid(OriginalText, PrevSpace + 1, NextSpace - 1 - PrevSpace)
If SecondText = 1 Then
SecondText = "1st "
ElseIf SecondText = 2 Then
SecondText = "2nd "
ElseIf SecondText = 3 Then
SecondText = "3rd "
Else: SecondText = SecondText & "th "
End If
cell.Value = FirstText & SecondText & ThirdText
Next cell

End Sub

You have to select the cell or cells that you want changed and then run the macro. Edit as needed.

_________________
Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-05-14 13:34

 

[Jack in the UK]

Love that code do you mind if i archive that one very sweet, but can iask the question was 1 2 3 and 33 so SORRY i have asked about US zips, but im lost i can add in 33 hassle free if there are 4 to 32 >>>>

>>>>>>>>>>>>>>>>??????????????????

that’s some code, i have a plan for this and so ask, but i not going to type 2500 variables.

IF us are 1 2 3 why ask 33?
and what about 10th 11th 12th there’s no th

can you advise me please,,,

 

[Al Chara]

Thanks Jack,

I really didn't test the code for a lot of different numbers. The following should work now. Please post with further questions.

Dim SecondText As Variant
Dim OriginalText As String, FirstText As String, ThirdText As String
Dim PrevSpace As Integer, NextSpace As Integer, LastSpace As Integer

For Each cell In Selection
OriginalText = cell.Value & " "
PrevSpace = 0
NextSpace = 0
LastSpace = 0

Do
PrevSpace = Application.WorksheetFunction.Search(" ", OriginalText, PrevSpace + 1)
NextSpace = Application.WorksheetFunction.Search(" ", OriginalText, PrevSpace + 1)
LastSpace = Application.WorksheetFunction.Search(" ", OriginalText, NextSpace + 1)
Loop Until LastSpace = Len(OriginalText)
OriginalText = cell.Value
FirstText = Left(OriginalText, PrevSpace)
ThirdText = Right(OriginalText, Len(OriginalText) - NextSpace)
SecondText = Mid(OriginalText, PrevSpace + 1, NextSpace - 1 - PrevSpace)
If SecondText = 11 Then
SecondText = "11th "
ElseIf SecondText = 12 Then
SecondText = "12th "
ElseIf SecondText = 13 Then
SecondText = "13th "
ElseIf Right(SecondText, 1) = 1 Then
SecondText = SecondText & "st "
ElseIf Right(SecondText, 1) = 2 Then
SecondText = SecondText & "nd "
ElseIf Right(SecondText, 1) = 3 Then
SecondText = SecondText & "rd "
Else: SecondText = SecondText & "th "
End If
cell.Value = FirstText & SecondText & ThirdText
Next cell

 

[Jack in the UK]

Ok i see it matters not, this is my way of thinking hope you did not mind me asking, but i was a tad lost not hot on US never been

cheers mate
i like this a lot ill let you know if my cunning plan works

 

[Al Chara]

Jack,

The example in question, "SW 1230 1 Ave", does not include a US zip code. It basically is saying the address is 1230 SouthWest 1st Avenue.

ZIP is an acronym for Zone Improvement Plan. A ZIP Code is a 5-digit code that identifies a specific geographic delivery area. ZIP Codes can represent an area within a state (an area that may or may not cross county boundaries), an area that crosses state boundaries (an unusual condition), or a single building or company that has a very high mail volume.

 

[sbgis63]

Al,

Thanks for the macro, i am sort of new with macros...when it comes to Excel i tend to work hard rather than work smart by performing manual processes.

I copied your macro as new macro which called NumbersFormat. I selected the appropriate column and ran the macro but it found an error in the line

NextSpace = Application.WorksheetFunction.Search(" ", OriginalText, PrevSpace + 1)

I appreciate your assistance, i sent you a sample copy of what i am up against if you wanted to run the macro and test it. As a macros novice i am not sure what the problem could be.

thanks
Scott

 

[Al Chara]

Scott,

I didn't know that your data was split up into separate columns for address, street, etc. This makes it a lot easier. Try the following:

Sub NumbersFormat()
For Each cell In Selection
If IsNumeric(cell.Value) = False Or cell.Value = "" Then GoTo 1
If cell.Value = 11 Then
cell.Value = "11th "
ElseIf cell.Value = 12 Then
cell.Value = "12th "
ElseIf cell.Value = 13 Then
cell.Value = "13th "
ElseIf Right(cell.Value, 1) = 1 Then
cell.Value = cell.Value & "st "
ElseIf Right(cell.Value, 1) = 2 Then
cell.Value = cell.Value & "nd "
ElseIf Right(cell.Value, 1) = 3 Then
cell.Value = cell.Value & "rd "
Else: cell.Value = cell.Value & "th "
End If
1 Next cell
End Sub

 

[sbgis63]

Thanks for your assistance sorry about the lack of direction. I have been experimenting myself with macros.

Let me show you. The issue is that numbers have to be assigned either a "th", "st", "rd", and "nd". The next issue is that the column consists of single digits (1-9), double digits (10-99), and triple digits (101-200).

Here is the macro for single digit for "th"

'Replace numbers 4-5-6-7-8-9 (single digit) with 4th-5th-6th, etc

Application.ReplaceFormat.NumberFormat = "0""th"""
Selection.Replace What:="4", Replacement:="4", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True
Selection.Replace What:="5", Replacement:="5", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True
Selection.Replace What:="6", Replacement:="6", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True

Here is the macro for double digit ending in "th"

'Replace double digit numbers with "th"s (10-11-12-13-14-15-16-17-18-19-20

Application.ReplaceFormat.NumberFormat = "00""th"""
Selection.Replace What:="10", Replacement:="10", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True
Selection.Replace What:="11", Replacement:="11", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True

So i am going to write the macro and test it and hopefully that will be the solution. I will post to let others know if it worked or not, so far i tested about 50 records and it seemed to work.

Thanks for your input!
Scott