Sum of all numbers, when text is within number

• I have a list of numbers such as follows:

1W
.5W
1W
1W
.25W
.25W
.5W

I need to add them up so I come up with 4.5W(in this example). Thanks.

Ed
[SIZE=6]*ficus*[/SIZE]
Thanks to all for making this website work!

• Assuming there is always 1 character at the end, this will work:

=SUM(--LEFT(L20:L26,LEN(L20:L26)-1))&RIGHT(L20,1)

and use CTRL-SHIFT-ENTER rather than just ENTER.

• I'm afraid I'm a bit lost. Where does this go? I don't really understand what all this is supposed to be. There will always be a W at the end of it.

Ed
[SIZE=6]*ficus*[/SIZE]
Thanks to all for making this website work!

• Hi,

=SUM(--LEFT(A1:A7,LEN(A1:A7)-1))&"W"

holding down Ctrl+Shift keys and Press Enter

hope it helps

• This is a formula you can put in any cell you like. Were you looking for a cell formula or VBA code?

Here is a small spreadsheet with the formula in it.

Files

• the only problem is that these are for an exact amount of cells, not a variable. I'm not sure how long the list is.

Ed
[SIZE=6]*ficus*[/SIZE]
Thanks to all for making this website work!

• Try this updated version of the spreadsheet. It assumes that there is no data below the column of numbers with the "W"s.

Files

• Hi Guys!

I may be missing something here, but why not just format the cells with the text to "add" the "W" following a number input alone?

What I mean is that the input in the cell will be "1", ".5" etc and not "1W", ".5W" etc.

On the Format>Cells>Number Tab, select Custom and in the Type: input box type #.##"W".

In the cell for the total, SUM(A1:??) will then add the numbers because they ARE numbers simply formatted to show the "W".

I must add, though, that I learned a lot from working through your solutions!

Kindest regards

Harry

• Quote from Foxxy1

the only problem is that these are for an exact amount of cells, not a variable. I'm not sure how long the list is.

[dr]*[/dr] would fix that.

However, I would go with Harry's suggestion.

• The problem (#2) is...

The W has to be there. I'm extracting info from another program and the W needs to be seen with the #. The previous solution worked. This isn't something I'm normally going to be doing anyway, so it's not too important as to how it gets done. Thanks for all the help everyone.

Ed
[SIZE=6]*ficus*[/SIZE]
Thanks to all for making this website work!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!