Formula returning the number of a cell that contains a number and symbol

**thesis** · 08-07-2012, 03:24 PM

I have two related formula questions:

First, is there a formula that will return the number of a cell that contains a number and a symbol?
For instance, I have many cells that contain something like "<100" and "<1.0."
I want a formula that will return just the number in the cell, 100 and 1.0 so I don't have to go through and delete all the "<."

Second, is there a formula that will return HALF of the number of a cell that contains a number and a symbol?
For instance, I have the cells again that contain "<100" and "<1.0."
I want a formula that will return HALF of the number in the cell and delete the "<" returning 50 and 0.5.

Could you help?

**Ace_XL** · 08-07-2012, 03:31 PM

This will separate numbers from any alphanumeric/special text characters

=LOOKUP(99^99,--("0"&MID($A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A2&1234567890)),ROW($1:$10000))))

If you need top halve that..simply use
=LOOKUP(99^99,--("0"&MID($A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A2&1234567890)),ROW($1:$10000))))/2

**CheshireCat** · 08-07-2012, 03:36 PM

Please note - The formulas suggested will remove decimal points. The results for your example would be 100 and 10.

**thesis** · 08-08-2012, 10:29 AM

That formula did not quite give the result I am looking for. I attached a sheet to try to further explain what I need.

**Ace_XL** · 08-08-2012, 10:34 AM

Remove the absolute references ($ sign) to Column C..

In M6 use
=LOOKUP(99^99,--("0"&MID(C6,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C6&1234567890)),ROW($1:$10000))))

drag across and below

@Cheshire - The formula will give numbers with decimal places too!

**thesis** · 08-08-2012, 10:46 AM

Great!
Now how would the second formula be written? I want to divide in half ONLY the numbers in the cells that contain "<."
In other words, only the cells that contain "<" will be divided by 2 and the "<" will be deleted.
Thank you thank you

**Ace_XL** · 08-08-2012, 10:51 AM

Try this..

=IF(ISNUMBER(C6),C6,LOOKUP(99^99,--("0"&MID(C6,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C6&1234567890)),ROW(1:10000))))/2)

**thesis** · 08-08-2012, 10:54 AM

Ahhh Thanks so much, I have spent so much time trying to figure this out!!

**Cutter** · 08-08-2012, 04:14 PM

@ thesis

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.