Removing text from a field, leaving a number

  • Thread starter Thread starter PK
  • Start date Start date
P

PK

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
 
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(replace(replace(replace(replace(x,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")
 
Back
Top