Deleting characters in text fields

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I have a table of data where the quantity is stored as a text value. Before
I convert it to a number value I need to delete some of the characters. Most
of the values appear like this: 25.000- but some appear as 5- with no decimal
points. Is there a way to to strip out all characters to the left of the
decimal point so that 25.000- ends up as 25? Or is there a way just to strip
out the " - " character to get 25.000?

Thanks,
Sean
 
Sean,

You can strip out the "-" by using the Replace command:

Replace("25.000-", "-", "")

Taking everything to the left of the first decimal, assuming that you may
not have a decimal might look like:

LEFT(Replace([Field], "-", "") & ".", INSTR(Replace([Field], "-", "") & ".",
".") - 1)

Actually, the following would be easier, since all you want is the integer
portion:

CINT(Replace([Field], "-", ""))

HTH
Dale
 
Sean -

The Val() function will do it for you. No need to strip out the hyphen --
Val() ignores non-numeric characters except a decimal point. Examples from
the debug (immediate) window:

x = "25.001-"
? val(x)
25.001

x = "25.000-"
? val(x)
25

HTH - Bob
 
Sean said:
I have a table of data where the quantity is stored as a text value. Before
I convert it to a number value I need to delete some of the characters. Most
of the values appear like this: 25.000- but some appear as 5- with no decimal
points. Is there a way to to strip out all characters to the left of the
decimal point so that 25.000- ends up as 25? Or is there a way just to strip
out the " - " character to get 25.000?
Click to expand...


Just use the Val function to convert the text to a number.
 
You might try the VAL function. It will turn 25.000- into 25 and 5- into 5.
It will also turn "" into zero and "xxx" into zero and "$22" into zero.

Val(YourField)

Val does expect a string and will error if you pass it a null. So for
safety you could use
Val("" & [Your field])



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top