Extracting a number from a mixed cell

[Dennis]

Excel 2003

What is the smartest way to extract a number from a cell that has mixed information?

Examples: (a column of cells can contain)

7980KWH 7,980 Kilowatt Hours
..8MCF .8 (thousand Cubic Feet)
FLAT Flat rate

Is there an array formula or something that will separate the numbers from the text?
As you can see the numbers positions vary (or are absent) per cell.

I have been experimenting with Find(), OR(), Search(), Mid(), but o no avail.

TIA Dennis

 

[Frank Kabel]

Hi
if your numbers always start at the beginning of a cell you may use the
following array formula (entered with CTRL+SHIFT+ENTER):

=--LEFT(A1,MAX(IF(ISNUMBER(--LEFT(A1,ROW(INDIRECT("1:1024")))),ROW(INDI
RECT("1:1024")))))

and to get the rest of the cell use the array formula
=MID(A1,MAX(IF(ISNUMBER(--LEFT(A1,ROW(INDIRECT("1:1024")))),ROW(INDIREC
T("1:1024"))))+1;1024)

 

[Dennis]

Thank you sooo much Frank. There is no way I could have come up with your solution as I am not that
competent - but I will learn why it works.

Yes, the number, if present, is always in the beginning of the cell information.

Dennis

 

[Dennis]

Frank,

I entered your formula in B1 referencing A1.

For 7920KWH I received 7 via the formula

What did I do wrong?

 

[Dennis]

Got it!

I forgot to CNTR-SHIFT-ENTER the second time I entered the formula.

Dennis

 

[Frank Kabel]

Hi Dennis
glad to hear it works for you

 

[JE McGimpsey]

Note that the first formula will give a #VALUE! error in the case that
there are no numbers at the beginning of the text.

You could use a UDF instead:

Function GetValue(sIn As String) As Double
GetValue = Val(sIn)
End Function

If you're not familiar with UDFs see David McRitchie's Getting Started
with Macros and UDFs":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dennis]

Frank,

I have been attempting to inhibit a #Value error display when there is no number.

Yes, I can use another "helper column" but I would like to use a single formula approach.

Example: This does not work but:

{=--LEFT(S22,MAX(IF(ISNUMBER(--LEFT(S22,ROW(INDIRECT("1:1024")))),ROW(INDIRECT("1:1024")))))}

=IF(ISNUMBER(Above Formula),Above Formula,"")

I do not know how to merge the above two formulas - as an array?

*********************************************************************************

Can your first formula be adapted to a extract a number from anywhere in a mixed cell?

*********************************************************************************

Dennis

 

[Dennis]

Good Morning!

I asked Frank before I saw your comment.

Is there a way to merge the formulas as stated in my re: (prior to Frank)?

What is the significance of the variable "sln" in your function.

I know how to add a function but not sure as to i.e. "sln".

Dennis

 

[JE McGimpsey]

It's the input argument. If your value was in cell A1, you call the
function as:

=GetValue(A1)

Then the text value of A1 is passed to the GetValue routine in the
variable sIn.

 

[Frank Kabel]

Hi Dennis
In general this would be the way to go. Problem is you would exceed the
maximum of 7 nested function. Therefore try the following:
1. Create a new name: goto 'Insert - Name - Define':
seq = ROW(INDIRECT("1:1024"))

2. Now use the following formula (array entered)
=IF(ISERROR(--LEFT(A1,MAX(IF(ISNUMBER(--LEFT(A1,seq)),seq)))),"",--LEFT
(A1,MAX(IF(ISNUMBER(--LEFT(A1,seq)),seq))))

 

[Dennis]

I entered the function into my module1.

Then in cell C1, I entered:

=GetValue(A1)

I get a #NAME! Error (whether there is/is not a number)

Thoughts?

TIA Dennis

 

[JE McGimpsey]

Not many - if it's in a regular code module it should be visible to that
workbook.

Couple of checks you've probably already done:

Did you enter the function in a module in the workbook you're calling it
from?
Did you enter it in a regular code module, not a worksheet or
ThisWorkbook module?
Did you spell the name correctly in your module?
Did you use Function rather than Sub?
Did you spell the name correctly in the cell?

 

[Dennis]

This is a direct Copy/Paste

Function GetValue(sIn As String) As Double
GetValue = Val(sIn)
End Function

It was placed into Module1 of Personal.xls which is Active

The cell into which it was entered is another W/S which is, of course, also active.

Cell C1 contains the formula =GetValue(A1)

Result = #NAME!

A1 contains "7980KWH"

Thanks, Dennis


*********************************************************

 

[JE McGimpsey]

Try:

=Personal.xls!GetValue(A1)

You have to qualify function names unless the function is in an add-in.

 

[Dennis]

That was the answer!

Thanks for your time & knowledge.

I am trying so hard to do my own thing but the subtleties are incredible. Like this situation,
which was only three lines of VBA code and still it was a challenge.

Well, I definitely remember next time.
If there is any way I could help you, let me know.

Dennis