Cell to display numeric and alpha, but only value the number

[erodriguez1214]

All,

I'm looking for a way to display both alpha and numeric values but be able to sum only the numeric value.

Example:

Cell A1 = 100 case
Cell A2 = 50 each
Cell A3 = feet 50

The alpha characters may sometimes come before the digits, and aren’t always with the same spacing (i.e. separated by a blank space).

My hope is that there is a way to do this without a VBA, as I have no clue how to use them.

Any help that can be offered is greatly appreciated.

 

[Ron Coderre]

Using your posted example...
This formula returns the consecutive numbers from the referenced cell:

B1: =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))

Copy that formula down through cell B3

In your example, the formulas return these values:
B1: 100
B2: 50
B3: 50


Is that something you can work with?

 

[erodriguez1214]

Thanks Ron. You're the man!