How to do condition with alphanumeric cell?

ninhkaist · Newbie Joined: 25 Jul 2012 Posts: 1

Hello guys,

I have cells that look like this:

0.55 Mw

1000Vdc

How can i do the comparison as if there is no alpha letter in it.

for example

=If(x>0.55, "a","b")

But the cell value is 0.55 Mw --> it shows error.

any help will be appreciated

thks

ken johnson · Posted: Wed Jul 25, 2012 10:29 pm Post subject:

One way is to use the VALUE and LEFT functions with the SEARCH function using a regular expression to search for the first non-numerical character.
You will need to go Tools|Options...|OpenOffice.org Calc|Calculate then place a tick in the "Enable regular expressions in formulae" checkbox for this to work.
=If(A1>0.55, "a","b") would need to be...

CSLam · Posted: Thu Aug 30, 2012 12:29 am Post subject:

I do not know what your data is, but they look like numbers with units associated, eg. 6kg. If this is the case, you may consider entering numbers only, and set your cell to add in the units. With this arrangement, your data will beco0me pure numbers, which is easy enough to handle.

To add in units in your cell, open the format cell menu, then use this :-
@"kg"

With this, if you input 1 in the cell, it will be shown as 1kg, while the cell is actually 1: easier input, eaiser comparison.

I hope this is what you want.

CSLam