If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Removing text from a field, leaving a number
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! |
#3
|
|||
|
|||
Removing text from a field, leaving a number
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! |
#4
|
|||
|
|||
Removing text from a field, leaving a number
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! |
Thread Tools | |
Display Modes | |
|
|