identifying words spelt incorrectly with numbers instead of letters

[organicmatter]

Hello

I have some data I need to clean.
It is in an address field the majority of the data is fine but there are some records with words that have been spelt with numbers:

2 9RAMBLEGATE
2 Forest V1EW
2 KEM8LE Close
2 BELLEMOT4TE Road

Is it possible using a query to find instances where this has happened so I can flag these?

Any help would be greatly appreciated

Tim

 

[CraigDolphin]

I think you'd have to create a custom function to parse the data.

Maybe something like (air code):

'place in a public vba module
Public function clean(dirty as string) as boolean
Dim place as integer
Dim countnumbers as integer

place = instr(1,trim(dirty)," ") 'find the first space in the string 

for place = place to len(trim(dirty))

if isnumeric(mid(trim(dirty),place,1)) then 'start looking for numbers after the first space
countnumbers = countnumbers + 1
End if
next

if countnumbers > 0 then
clean = false
Else
clean = true
End if
End function

then in a query you could use something like: Spelling: IIf(clean([addressfield]),"Ok","Mispelled") to flag the field.

 

[PeterF]

I think it would be easier to select the record where the field has numbers, this can be done by putting like "*#*" in the selection criterea.
Then you can manually edit them.

 

[neileg]

You could also use Val() to return the number. It would be a null if there were no numbers present.

 

[CraigDolphin]

Very nice, Neil. Much more straightforward

 

[neileg]

I'm lazy, I don't do complicated stuff!

 

[organicmatter]

ahhrrgg the pain of red wine the day after….

thanks very much for the response’s only have a few thousand when using the value or like options so will use one of those and flag by hand, but slowly as the screen it burns my eyes.

best wishes

Tim

 

[CraigDolphin]

Tim,

Almost all street addresses will start with a street number so you can;t just use Val() or Like "*#*" on the entire string or it will come back with almost every address.

The function above could be simplified by using the val function to apply it to the bit of the string that comes after the first space. Regardless, if you follow my earlier instructions much of the flagging will be done for you. However, the code above doesn't allow for situations like PO Boxes so it might need to be modified for that. Maybe something like:

'place in a public vba module
Public function clean(dirty as string) as boolean
Dim place as integer

if InStr(1,UCase(dirty)," BOX ") = 0 then 'the address is not a PO Box
   
   place = instr(1,trim(dirty)," ") 'find the first space in the string 
   if not IsNull(Val(right(dirty,len(dirty)-place))) then 
      'there is a number in the rest of the string!
      clean = false
   Else
      'there is no number in the rest of the string
      clean = true
   End if

Else ' the address is a PO Box
   'since numbers are supposed to follow the PO Box part of the string, we 
   'can't determine if a typo has occured. This code assumes the address is 
   'ok. Change the word true to false on the next line if you want to presume 
   'they are bad instead.
   clean = true
End if
End function

As I mentioned before: put the code in a public code module, and in a query based on your original table include a field with the address, and another field with the expression MyFlag: IIf(clean([addressfield]),"Ok","Mispelled").
You can use the criteria "Mispelled" in that query field to restrict the results to only those addresses that the function thinks are bad.