Extract Numbers from text and numeric string

[Jeff Byrd]

We pull data that randomly has a letter placed at the begining of a string
Example
Some are as follows:
A1535464564634
R54641646436434
Others are Numeric only:
1654646464654
31236464646778

I need to extract the Numeric portion.
How can I extract only the numeric portion.

Thanks

 

[Tom Ellison]

Dear Jeff:

Yeu need first a test whether there is a letter there or not:

IIf(YourColumnName > "9"

would be a start. Then choose to extract it one of 2 ways, with or without
the leading character.

IIf(YourColumnName > "9", Mid(YourColumnName, 2), YourColumnName)

I think that may do it, but start with a query that displays the actual
value in the query and then displays this thing:

SELECT YourColumnName,
IIf(YourColumnName > "9", Mid(YourColumnName, 2), YourColumnName) AS
NumberValue
FROM YourTable

Replace the actual name of your column and table throughout what I have
written.

Tom Ellison

 

[Jeff Byrd]

I don't find that IIf(YourColumnName > "9" tells mw if there is a letter at
the begining.

Jeff

Dear Jeff:

Yeu need first a test whether there is a letter there or not:

IIf(YourColumnName > "9"

would be a start. Then choose to extract it one of 2 ways, with or
without the leading character.

IIf(YourColumnName > "9", Mid(YourColumnName, 2), YourColumnName)

I think that may do it, but start with a query that displays the actual
value in the query and then displays this thing:

SELECT YourColumnName,
IIf(YourColumnName > "9", Mid(YourColumnName, 2), YourColumnName) AS
NumberValue
FROM YourTable

Replace the actual name of your column and table throughout what I have
written.

Tom Ellison

 

[Tom Ellison]

Dear Jeff:

That part of my post was only a code fragment. I was leading up to the
complete solution. Please try the whole thing.

It doesn't even have balanced parens. I was illustrating my starting point
for the solution, not giving a partial solution.

Tom Ellison

I don't find that IIf(YourColumnName > "9" tells mw if there is a letter at
the begining.

Jeff