Use Field Calculator to strip out "1/2" in an Address

BrianO_keefe

I'm busy processing 200,000+ addresses that incorrectly have an old outdated sub-designation in use. Our City no longer uses the "1/2" in our addresses. We have updated them to a sub-designation of "REAR" instead.

So whereas previous an address might have been "1234-1/2 East Main Street South" it is now listed as "1234 East Main Street South Rear" and I am trying to use the Field Calculator to remove this.

Here is my code.

DIM StripStart, StripEnd
DIM DataToStrip, DataToStripRight, DataToStripLeft, DataToStripLen
DIM WhatToStrip, WhatToStripLen
DIM NewDataSet

WhatToStrip = '1/2'
WhatToStripLen = Len( WhatToStrip )

DataToStrip = [Address]
DataToStripLen = Len( DataToStrip )

StripStart = InStr( 1, DataToStrip, WhatToStrip )
StripEnd = StripStart + WhatToStripLen

DataToStripRight = Right( DataToStrip, ( DataToStripLen - StripEnd ) )
DataToStripLeft = Left( DataToStrip, StripStart )

NewDataSet = DataToStripLeft + ' ' + DataToStripRight

I am getting a 999999 error that says it is undefined. Can anyone take a look at this and see what I'm doing wrong?

Message was edited by: Brian O'keefe Cleaned up the Code. It has compiled into a single line due to missing BR tags.

BrianO_keefe

I figured it out. It has to do with the TYPE of VB used in the Field Calculator.

The apostrophe ' that I was using signified a COMMENT not a VARIABLE or VALUE.

Here is the corrected finished code for stripping out a subsection of text from a text field.

DIM StripStart, StripEnd
DIM DataToStrip, DataToStripRight, DataToStripLeft, DataToStripLen
DIM WhatToStrip, WhatToStripLen
DIM NewDataSet

' Example address provided
' 1003 1/2 N EVANSTON AVE

WhatToStrip = "1/2"
WhatToStripLen = Len( WhatToStrip )                         ' = 3

DataToStrip = [Address]
DataToStripLen = Len( DataToStrip )                         ' = 23

StripStart = InStr( 1, DataToStrip, WhatToStrip )     ' = 6
StripEnd = StripStart + WhatToStripLen                    ' = 9

DataToStripRight = Right( DataToStrip, ( DataToStripLen - StripEnd ) )
DataToStripLeft = Left( DataToStrip, ( StripStart - 1 ) )

' 1003 N EVANSTON AVE
NewDataSet = DataToStripLeft + DataToStripRight

I added in the comments to help explain the code.