formula to extract numbers

[RPrasad]

Hi,

My data in one column "Name" looks like this
Travel Eur 02-09 for 128789

Earlier there were only 4 digits at the end and I was using the below expression to get the numbers into another column. Now the number of digits vary, and I need only the numbers in another column. These numbers will be always at the end.

Right(TravelSAP!Name,4)

please help me to put the formula to extract the number part from the last irrespective of number of characters.

 

[james_lankford]

SELECT 
[Name],
mid( [Name], instrrev ([Name], "for ") + 4)
FROM your_table

as long as the chars are preceeded by
"for "
it should work

 

[RPrasad]

James,
only numbers at the end remains constant, not the explanation prior to that. And I need to extract full part of the numbers i.e. 128789, in another line it could be 12878 etc. "for" is not constant which may not be available in all the lines.

 

[james_lankford]

so the numbes always appear at the end ?
and is there always a space before the numbes ?
is so, just search for a space instead of for "for "

SELECT 
[Name],
mid( [Name], instrrev ([Name], " ") + 1)
FROM your_table

 

[RPrasad]

Thank you James, that works