Remove Leading Numbers from Text String

[callie_sunrise]

Hi,

I know this has been asked so may times before but I am a complete novice to
coding.

Basically I have around 120K rows in a table. The data was exported from an
Ingres database but for some fields in the table it decided to add a number
at the start of the string.
e.g.:
junction_with (field name)
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD

I need to remove the numbers at the start of the string. I'm guessing I
need to use a combination of TRIM and either RIGHT or LEFT but I have no idea.

Any suggestions welcome!!

Thanks all,

 

[Tom van Stiphout]

On Tue, 17 Jun 2008 05:08:00 -0700, callie_sunrise

I am assuming you don't know how many numerics are at the start of the
string. We shall remove them one at a time:
update MyTable
set junction_with = Mid$(junction_with, 2)
where IsNumeric(left$(junction_with, 1, 1))

Run this several times (on a copy of your db) until the numerics are
gone.

-Tom.

 

[Wayne-I-M]

What shouold it be

14ALUM ROCK ROAD
or
14 ALUM ROCK ROAD
or
4 ALUM ROCK ROAD
or
ALUM ROCK ROAD
or
??

 

[Rui]

Hi

the expression Right("14ALUM ROCK ROAD", Len(strTemp) - 2) should do the
trick.

if numbers are diferent from row to row then I would do a function and add
the following code (where strTemp is the string you want to manipulate:

Dim i as integer
For i = 1 To Len(strTemp)
If Not IsNumeric(Mid(strTemp, i, 1)) Then
Debug.Print Right(strTemp, Len(strTemp) - i + 1)
End
End If
Next i


Take care,
Rui

 

[callie_sunrise]

Hi,

I should be left with just Alum Rock. Bearing in mind these are street
names so there is an assortment of streetnames in the 120K odd rows. All the
street names have been prefixed with a two digit number (the number of
characters in the street name).

Hope this helps.

Thank you,

 

[callie_sunrise]

What if there are over a thousand different street names? How can I remove
the numbers for all 120K rows without specifying each street name?

Thank you,

 

[callie_sunrise]

Hi,

I know there is a two digit number (the number of characters in the street
name) for each string.

Thank you,

 

[Klatuu]

Here is an expression that will strip off the numeric part of the string:
replace(x,Cstr(val(x)),"")

Where x is the string you want to take the number off from.

 

[callie_sunrise]

Also,

Do I do this as an update query or VBA or Macro?

 

[Rui]

Just read your other comments

you could use this on a query

Right([fieldName], Len([fieldName]) - 2)

where [fieldname] is the column that you what to deal with.

 

[Rui]

Alternatively you could create a new module in VBA and insert the following:

Function myFunction(strTemp) As String
Dim i As Integer
For i = 1 To Len(strTemp)
If Not IsNumeric(Mid(strTemp, i, 1)) Then
myFunction = Right(strTemp, Len(strTemp) - i + 1)
Exit For
End If
Next i
End Function


this way you could use it anywhere in your queries, forms or reports just
like any other inbuilt function of Access (i.e. just like MID, LEFT, UCASE,
etc..)

Cheers
Rui

Just read your other comments

you could use this on a query

Right([fieldName], Len([fieldName]) - 2)

where [fieldname] is the column that you what to deal with.

Also,

Do I do this as an update query or VBA or Macro?

 

[Douglas J. Steele]

Or

Mid([fieldName], 3)

which avoids having to determine the length of the string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Just read your other comments

you could use this on a query

Right([fieldName], Len([fieldName]) - 2)

where [fieldname] is the column that you what to deal with.

Also,

Do I do this as an update query or VBA or Macro?

 

[callie_sunrise]

Hi Douglas,

How would I use this, Module (VBA) or in an update query?

Thanks,

Or

Mid([fieldName], 3)

which avoids having to determine the length of the string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Just read your other comments

you could use this on a query

Right([fieldName], Len([fieldName]) - 2)

where [fieldname] is the column that you what to deal with.

Also,

Do I do this as an update query or VBA or Macro?

:

Hi

the expression Right("14ALUM ROCK ROAD", Len(strTemp) - 2) should do
the
trick.

if numbers are diferent from row to row then I would do a function and
add
the following code (where strTemp is the string you want to manipulate:

Dim i as integer
For i = 1 To Len(strTemp)
If Not IsNumeric(Mid(strTemp, i, 1)) Then
Debug.Print Right(strTemp, Len(strTemp) - i + 1)
End
End If
Next i


Take care,
Rui



:

Hi,

I know this has been asked so may times before but I am a complete
novice to
coding.

Basically I have around 120K rows in a table. The data was exported
from an
Ingres database but for some fields in the table it decided to add a
number
at the start of the string.
e.g.:
junction_with (field name)
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD

I need to remove the numbers at the start of the string. I'm
guessing I
need to use a combination of TRIM and either RIGHT or LEFT but I have
no idea.

Any suggestions welcome!!

Thanks all,

 

[callie_sunrise]

Hi Rui,

I understand creating a module, but how would I specify the column name?

Thanks,

Alternatively you could create a new module in VBA and insert the following:

Function myFunction(strTemp) As String
Dim i As Integer
For i = 1 To Len(strTemp)
If Not IsNumeric(Mid(strTemp, i, 1)) Then
myFunction = Right(strTemp, Len(strTemp) - i + 1)
Exit For
End If
Next i
End Function


this way you could use it anywhere in your queries, forms or reports just
like any other inbuilt function of Access (i.e. just like MID, LEFT, UCASE,
etc..)

Cheers
Rui

Just read your other comments

you could use this on a query

Right([fieldName], Len([fieldName]) - 2)

where [fieldname] is the column that you what to deal with.

Also,

Do I do this as an update query or VBA or Macro?

:

Hi

the expression Right("14ALUM ROCK ROAD", Len(strTemp) - 2) should do the
trick.

if numbers are diferent from row to row then I would do a function and add
the following code (where strTemp is the string you want to manipulate:

Dim i as integer
For i = 1 To Len(strTemp)
If Not IsNumeric(Mid(strTemp, i, 1)) Then
Debug.Print Right(strTemp, Len(strTemp) - i + 1)
End
End If
Next i


Take care,
Rui



:

Hi,

I know this has been asked so may times before but I am a complete novice to
coding.

Basically I have around 120K rows in a table. The data was exported from an
Ingres database but for some fields in the table it decided to add a number
at the start of the string.
e.g.:
junction_with (field name)
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD
14ALUM ROCK ROAD

I need to remove the numbers at the start of the string. I'm guessing I
need to use a combination of TRIM and either RIGHT or LEFT but I have no idea.

Any suggestions welcome!!

Thanks all,

 

[callie_sunrise]

Hi Tom,

I get an error message when trying to run your query:
"Wrong number of arguments used with function in query expression
IsNumeric(left$(junction_with, 1, 1)).

Thanks,