1

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.

6
  • Are you trying to say it needs to remove the L, R, and T, and ADD those 3 fields AFTER they're removed? Jan 28, 2014 at 8:48
  • Thanks for the reply. Basically, the text is not required for the counting purposes but has to remain there for other reasons which is why i can't just have a number in the cell. So it must just be ignored as if it wasn't there. Does that clear it up?
    – user293932
    Jan 28, 2014 at 8:54
  • Are you saying A3 is the cell number and 1L is the string thats entered in the field? for the example A3="1L". if so what exactly are you trying to SUM from those 3 values. Jan 28, 2014 at 8:58
  • Yes exactly. This document is used to list the furniture per room in a 200 bedroom hotel. For items like a chair, or a table the installed qty is simply a number, like 1 or 2. But for items like the minibar, cupboard or safe i need to include an orientation such a 1L or 1R to show what is in that room. The purpose of counting all of these 1's is so that i can compare (in another column) what is purchased versus what is installed. If they are equal, a conditional format makes the cell green. If not, it's red. Thanks for your help.
    – user293932
    Jan 28, 2014 at 9:31
  • Are the letters always at the end and always 1 character? (e.g. There cannot be something like 100BT)
    – Jerry
    Jan 28, 2014 at 9:35

2 Answers 2

1

You could try this formula:

=SUM(IFERROR(LEFT(A1:J10,LEN(A1:J10)-1),0)*1)

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 or R but can have bare numbers like 10, then you could use this instead:

=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(A1:J10,"R",""),"L","")*1,0))

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:

=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE($C4:$R4,"R",""),"L","")*{1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0},0))

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:

=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE($C4:$R4,"R",""),"L","")*{0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1},0))

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)

4
  • Hi Jerry, thanks so much for your reply. I think that first formula is going to be my solution because it just worked for me... but i just need the most efficient way to write it now. I can't use a range because for every single line item i have 2 columns, 1 for required quantity and 1 for installed quantity. So i am only counting every second cell in the range. To be specific, the cells i need to count will always be (C18, E18, G18, I18, K18, M18, O18, Q18). How can i modify =SUM((IFERROR(LEFT(C18,LEN(C18)-1),0)*1) to count only those cells? Appreciate the help, thanks.
    – user293932
    Jan 28, 2014 at 11:27
  • @user293932 Hmm, that'd be a bit difficult. Is it possible to see a sample of your spreadsheet? There might be a better way of comparing those figures than to sum them like that, through a formula and conditional formatting for example.
    – Jerry
    Jan 28, 2014 at 13:16
  • Sure, here's a [link]db.tt/rnwCmstW i use a mac so when you open it the formatting may be a bit weird. Even though it's a cumbersome formula it seems to be doing what i want it to so thanks again to you and everyone else for the help.
    – user293932
    Jan 28, 2014 at 14:02
  • @user293932 Ohh, there's actually a little trick you can do. I'll update my answer. In the meantime, I also updated the file you linked with some little improvements in my style you can get here, hoping they work on mac. Basically, I used a single conditional formatting for the whole range.
    – Jerry
    Jan 28, 2014 at 15:45
0

There isn't a magic formula to convert these cells to numbers. You'll have to use string manipulation to do it. If your numbers are always one digit, you can use:

=VALUE(LEFT(A2,1))

to extract the leftmost character and convert it to a number. If your numbers are variable-length, but there is always one letter on the right, you can use:

=VALUE(LEFT(A2,LEN(A2)-1))

Hope that helps. A good regex parser is one of the major things missing from Excel. It's worth noting that it's really easy to do it in Google Spreadsheets, using something like: (untested)

=VALUE(REGEXREPLACE(A2,"[A-Z]",""))

5
  • Thanks so much for your reply. I will give it a test now. My document is actually for counting the furniture in the rooms of a hotel so the cells i need to count will always be either 1L or 1R which means '1 x left handed cupboard' or '1 x right handed cupboard'. I cannot remove the letter because it tells me which orientation that room requires. It will only ever be L or R and preceded by the number 1.
    – user293932
    Jan 28, 2014 at 9:28
  • Hi Ben. Your formula worked perfectly with only 1 problem. Of the cells that are being counted, sometimes 1 of them may be empty (if that item is not required in a specific room) and in this case the formula is only returning #### as a value. Is there way to exclude a cell that is empty?
    – user293932
    Jan 28, 2014 at 9:42
  • This is my formula: =SUM(VALUE(LEFT(C18,1)),VALUE(LEFT(E18,1)),VALUE(LEFT(G18,1)),VALUE(LEFT(I18,1)),VALUE(LEFT(K18,1)),VALUE(LEFT(M18,1)),VALUE(LEFT(O18,1)),VALUE(LEFT(Q18,1)))
    – user293932
    Jan 28, 2014 at 9:42
  • That's quite a cumbersome formula. I would have an extra cell for each one and just sum them at the end, or you can use @Jerry's array formulae. And as he shows, you can use IFERROR to parse the empty cells. Jan 28, 2014 at 10:07
  • It is a terrible formula and i am not proud of it! As much as possible i would prefer to keep all the calculations in 1 cell (the total column) so i think Jerry's formula will work if i just find the correct syntax for my specific range. Thanks very much for your help.
    – user293932
    Jan 28, 2014 at 11:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .