Creating Masks or Formats to Display Leading Characters of a Text String

[DigitalAdrenaline]

Hi, If it's uncomplicated to achieve, I'd like to display all characters to the left of a decimal place in a text string. I know there's expressions to extract and trim but was just curious of this was possible. Thanks.

[pbaldy]

Try the InStr() function to find the decimal within the Left() function.

[DigitalAdrenaline]

Cheers pbaldy, will look at that. Thx.

[CJ_London]

or use the int function

int("123456.345466")

returns 123456

[isladogs]

Just one word of caution about using the Int function
It works fine with positive numbers but may not give what you want with negative values
Int(1234.56)=1234 BUT Int(-1234.56) = -1235

You may find the Fix function better for your purposes
Fix(1234.56)=1234 AND Fix(-1234.56) = -1234

If you experiment with different decimal point values, you'll see the result remains the same
e.g. Int(-1234.01) = -1235 ; Fix(-1234.01) = -1234

BTW the "" marks aren't needed with either function as you are dealing with numbers

[CJ_London]

I'd forgotten about Fix

I included the quotes because the OP said he had a text string and I wanted to demonstrate the int function works on (valid) strings

[DigitalAdrenaline]

Hi Ridders, It seems reading Google that Int, Round & Fix are numeric centric and don't apply to text strings. That's ok, it wasn't critical. Cheers, guys. My learning curve goes up a notch. Several in fact.

[isladogs]

Ajax is of course correct in stating that Int will work (with quotes) if the text string is just a number like 1234.56 ... as will Fix.
I was assuming you had already stripped the number part out of your string.

It is certainly the case that the functions Int, Fix and Round will not work on a text string like 'A is 100.53 km from B' unless you do first separate out the 100.53 part.
You could do that by e.g. searching for the first numeric character 0-9 or + or - then extracting everything before the following decimal point

[CJ_London]

It seems reading Google that Int, Round & Fix are numeric centric and don't apply to text strings

don't believe everything you read - if in doubt, try it.

if you have something like '123.456 km' use the val function first

fix(val("123.456 km"))

will return 123

works for any string that starts with numerical values

[DigitalAdrenaline]

Ahh, ok. All my strings are text. But that function's handy to know. Thx.

[CJ_London]

a string by definition is text the question should be why are you storing numbers as text? You can't sort numerically, table size is bigger, indexing slower