Formulas that see number but not text?

[mommus]

Hi folks,

I'm putting together a spreadsheet of construction estimates, and looking to speed it up a bit by adding some formulas.

I'd like to find a way to multiply a measurement - sometimes metres, sometimes square metres and sometimes cubic metres - by a rate (in UK£)

There's no real way to sort the linear, area and volume measurements so they're all grouped, as it's laid out by the demolition and construction phase, so it would be ideal if I can take a measurement - say square metres (10m2) for example - and multiply it by a rate of £40 per m2.

Currently the total would come out as !VALUE error because the 'm2' part would upset the formula.

Is there a way to either format the measurement cell (10m2) to add the 'm2' suffix automatically, or (ideally) get the formula to ignore the 'm2' and just read the '10'?

I should add that all the measurements (m, m2, m3) and sometimes other units, need to be in the same column, so I can't really just have one column for linear measurements, one for area etc

thanks in advance

M

[RoryOF]

Why not put each type of measurement figure into its own column, hide these columns (whose figures you use for the calculation) but copy the figures forward to a display column?

[Zizi64]

Yes it is better to put the Units into the adjacent cells.

...or you can operate with Cell styles.
You can create and apply some user defined cell styles for every unit that you want to use.
The formatting strings are not parts of the formatted numbers. See my sample file:

Cell styles and Units.ods

(10.4 KiB) Downloaded 86 times

I just created a parent style named 'MyUnits', and four child styles: 'Dollar', meter', 'meter2' and 'meter3'.
The number format was created based on the default format code of the "Currency".
(The backgrund color is a modified property of my styles, too.)