[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
ninhkaist
Newbie

Joined: 25 Jul 2012
Posts: 1

 Posted: Wed Jul 25, 2012 6:49 pm    Post subject: How to do condition with alphanumeric cell? 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
Super User

Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

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...
 Code: =IF(VALUE(LEFT(A1,SEARCH("[:alpha:]+",A1)-1))>0.55, "a","b")

Ken Johnson
_________________
CSLam
General User

Joined: 08 Aug 2012
Posts: 29
Location: Hong Kong

 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum