Removing text from a field, leaving a number

Help!
I have been running around in circles with this one...

I have a field which contans text and numbers. I need to extract the
number, removing the text completely. Here is the tricky part: there is a
variable number of text characters before and after the number (sometimes no
text after the number).

Example:
abc123def (i want 123)
abcdefg456 (I want 456)
a789 (I want 789)

PLEASE help! I am pulling my hair out!

If you can spot the start of the number, it becomes simple:

? val( Mid( "abcd123efg", 5))
123


but that is probably the greatest part of the problem.


Still doable to do it in SQL, though, if you have a driver table, Iotas, one
field, iota, its primary key, with values from 0 to, at least, 255:


SELECT originalField, MAX( val ( mid ( originalField, iotas.iota) ))
FROM yourTable INNER JOIN iotas
ON iotas.iota = len(yourTable.originalField)
GROUP BY originalField


which is based on the fact that val( "a" ) returns 0.

I also assumed your original field do not have NULL value.




Vanderghast, Access MVP


"PK" wrote in message
...
Help!
I have been running around in circles with this one...

I have a field which contans text and numbers. I need to extract the
number, removing the text completely. Here is the tricky part: there is a
variable number of text characters before and after the number (sometimes
no
text after the number).

Example:
abc123def (i want 123)
abcdefg456 (I want 456)
a789 (I want 789)

PLEASE help! I am pulling my hair out!

Michel,

Absolute GENIUS!

Please run for president. You have my vote.

-PK



"Michel Walsh" wrote:

If you can spot the start of the number, it becomes simple:

? val( Mid( "abcd123efg", 5))
123


but that is probably the greatest part of the problem.


Still doable to do it in SQL, though, if you have a driver table, Iotas, one
field, iota, its primary key, with values from 0 to, at least, 255:


SELECT originalField, MAX( val ( mid ( originalField, iotas.iota) ))
FROM yourTable INNER JOIN iotas
ON iotas.iota = len(yourTable.originalField)
GROUP BY originalField


which is based on the fact that val( "a" ) returns 0.

I also assumed your original field do not have NULL value.




Vanderghast, Access MVP


"PK" wrote in message
...
Help!
I have been running around in circles with this one...

I have a field which contans text and numbers. I need to extract the
number, removing the text completely. Here is the tricky part: there is a
variable number of text characters before and after the number (sometimes
no
text after the number).

Example:
abc123def (i want 123)
abcdefg456 (I want 456)
a789 (I want 789)

PLEASE help! I am pulling my hair out!

Just for fun, this may when the absurd expression of the month award, but it
will take all numberic characters out of a string regardless of where they
are or whether they are contiguous:
replace(replace(replace(replace(replace(replace(re place(replace(replace(replace(x,"0",""),"1",""),"2 ",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8", ""),"9","")
--
Dave Hargis, Microsoft Access MVP


"PK" wrote:

Michel,

Absolute GENIUS!

Please run for president. You have my vote.

-PK



"Michel Walsh" wrote:

If you can spot the start of the number, it becomes simple:

? val( Mid( "abcd123efg", 5))
123


but that is probably the greatest part of the problem.


Still doable to do it in SQL, though, if you have a driver table, Iotas, one
field, iota, its primary key, with values from 0 to, at least, 255:


SELECT originalField, MAX( val ( mid ( originalField, iotas.iota) ))
FROM yourTable INNER JOIN iotas
ON iotas.iota = len(yourTable.originalField)
GROUP BY originalField


which is based on the fact that val( "a" ) returns 0.

I also assumed your original field do not have NULL value.




Vanderghast, Access MVP


"PK" wrote in message
...
Help!
I have been running around in circles with this one...

I have a field which contans text and numbers. I need to extract the
number, removing the text completely. Here is the tricky part: there is a
variable number of text characters before and after the number (sometimes
no
text after the number).

Example:
abc123def (i want 123)
abcdefg456 (I want 456)
a789 (I want 789)

PLEASE help! I am pulling my hair out!