OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to do condition with alphanumeric cell?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
ninhkaist
Newbie
Newbie


Joined: 25 Jul 2012
Posts: 1

PostPosted: Wed Jul 25, 2012 6:49 pm    Post subject: How to do condition with alphanumeric cell? Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Wed Jul 25, 2012 10:29 pm    Post subject: Reply with quote

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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
CSLam
General User
General User


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

PostPosted: Thu Aug 30, 2012 12:29 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group