Keep the first 6 characters/numbers (Trim / Regex ??)

[G_SAND]

Hi.

I have a data set that can be numbers or string:

0.385
19.656
33.133
270.268
319.694
650.000
157.500
13499.460

For various reasons I only want the first 7 characters/numbers.

So the only 1 that would change in the list above is the last one, to 13499.46. I can't change the fixed decimal as there are some smaller numbers that need to be at 3 decimal places. 

I'm not great when it comes to working out the Trim functions or RegEx so any help would be great.

Thanks

[KaneG · Accepted solution]

Hi @G_SAND,

No need to over-complicate...

Left(ToString([Field]),6)

[MarqueeCrew · Accepted solution]

If it can be a string, then you'll only need:

Left([field],6)

:)

[jdunkerley79]

Just for the sake of a numerical approach:

ROUND([Value],POW(10,MAX(3,CEIL(LOG10([Value])))-6))

It rounds rather than truncates so 13499.46 becomes 13499.5