I have searched high and low and can't find a simple solution for this. Hopefully it exists.
I have 3 random cells that contain alphanumeric strings. Eg: A3="1L" and B12="2R" and H5="3T" etc. etc. Note: These cells are not in a range, they are random.
Is there a formula in excel that can sum the numeric values of these 3 cells? ie. arrive at a sum or 6 for these cells.
Appreciate the help,
Thanks,
Sean.
Best Answer
You could try this formula:
Adjust the range as necessary.
This will blanket a whole range, remove the last character of each, then add them together.
If you really have only
L
orR
but can have bare numbers like10
, then you could use this instead:NOTE: Both of the above formulae should be called with Ctrl+Shift+Enter after having input them in a cell since they are array formulae.
EDIT: To get alternate columns, you can use this:
Again, you need to use Ctrl+Shift+Enter for it to work properly.
For the next column (the ones that should be installed), you simply change the order of the 1's and 0's:
Notice that there is a number for each of the cells within the range (C4:R4 has 16 cells, therefore there are 8 1's and 8 0's)