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.

  • Are you trying to say it needs to remove the L, R, and T, and ADD those 3 fields AFTER they're removed? – Sickest Jan 28 '14 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 '14 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. – Sickest Jan 28 '14 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 '14 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 '14 at 9:35
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)

  • 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 '14 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 '14 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 '14 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 '14 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]",""))

  • 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 '14 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 '14 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 '14 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. – benshepherd Jan 28 '14 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 '14 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.