Find $

M

Max

I have data that starts with $ which is easy to find.

But sometime the $ is blended in the text

please note this data is in Access??? But if I have to move it to excel ok...

Hello-$10.00 SPEC COPAY
Hello - $30.00 SPEC COPAY
VARIOUS COPAYS,INCL. $10 SPEC
Hello $20 SPEC COPAY / $0 FOR KIDS

How can I pick in a new column as follow
$10
$30
$10
$20

Thanks
 
J

John Spencer

You could use an expression like the following to get the numeric value.

Val(Mid(SomeField & "",Instr(1,SomeField,"$")))

IF you wanted a string with the leading $ then you could use the format
function or you could append the $ to the beginning of the value.

One problem, is that if you had a string like
Hello $20 SPEC COPAY / $10 FOR KIDS / $3.50 for infants

You would only get the 20 and not the 10 or 3.50/

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

John Spencer

Darn, I messed up the expression.

Val(Mid(SomeField & "", Instr(1,SomeField,"$")+1))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Top