Honda XRV Forum banner

1 - 16 of 16 Posts

·
Premium Member
Joined
·
9,523 Posts
Discussion Starter · #1 ·
Do we have any Excel guru's about with time on their hands?

I trying not to resort to VBA to do this, but can't see a way using normal Excel functions.

The problem is quite simple. I have a column of data that will be a mixture of text. I need to total the column in certain circumstances and derive the value from the text, ignoring the H prefix.

E.g.

CX120089
CX128765
H7
H7
H3.5
CX128766

And I want to total up the cells that begin with H.

So in this case the answer should be 17.5.

I should also say that I want to do this without splitting out the column in to other columns. I know I can do it that way, but space is at a premium so I'm trying to do it all in one formula in one column.

Thanks
Dave
 

·
A Dad with longlegs
Joined
·
226 Posts
Try formatting the cells like this "H"0.00 and then just type the numerical value in. The H will be displayed and the cell will act as if its just a number.
 

·
Registered
Joined
·
6,174 Posts
You could always split it out to another column then hide the column. That way you'll be able to use some straightforward formulae to get the numeric if the first character is an 'H' and then just sum the entire range putting the result wherever you want.
 

·
Premium Member
Joined
·
9,523 Posts
Discussion Starter · #4 ·
Try formatting the cells like this "H"0.00 and then just type the numerical value in. The H will be displayed and the cell will act as if its just a number.
Tried that it doesn't seem to work.
It just sees it as a character field even formatted as a number.
 

·
Premium Member
Joined
·
9,523 Posts
Discussion Starter · #5 ·
You could always split it out to another column then hide the column. That way you'll be able to use some straightforward formulae to get the numeric if the first character is an 'H' and then just sum the entire range putting the result wherever you want.
Well I could do, but that's a cludge. :D
I'm convinced there's a way to do it elegantly with a formula but I just can't get it work.
 

·
Registered
Joined
·
4,811 Posts
I can't claim to be an expert, but my solution would have been similar to AlanH's.

What's the problem with that solution? If the problem is that you don't want to alter the original spreadsheet, then create another spreadsheet where you split out the numeric part, and sum it in the original sheet.

If it's purely about producing an elegant solution then maybe it can be done with a macro? Otherwise I'm flummoxed.
 

·
Registered
Joined
·
4,811 Posts
Hmmm...

If you had a fixed number of cells, you could CONCATENATE them into a long string of "7", " + ", "7", " + ", "3.5" and evaluate the string.

To get those numeric values you'd have to use something like =IF(LEFT(cell)="H",MID(cell,2,99),"0") Unfortunately you'd have to hard-code each cell reference and you'd end up with a ridiculously long formula! It wouldn't work if you had a variable number of cells.

Still not very elegant, sorry.
 

·
Premium Member
Joined
·
9,523 Posts
Discussion Starter · #8 ·
Exactly the same situation that I got to.
So yes I would need it to be flexible.
I think I'll write some VBA to loop through the column and inject the value in to a cell.
Thanks all.
 

·
A Dad with longlegs
Joined
·
226 Posts
Tried that it doesn't seem to work.
It just sees it as a character field even formatted as a number.
Worked for me, custom format. Don't type the "H".
 

·
A Dad with longlegs
Joined
·
226 Posts

·
Registered
Joined
·
4,811 Posts
Ah, clever... if you're going to type the codes in. I wonder if it would still work if the codes were imported from a file?
 

·
whys the rum always gone?
Joined
·
17,680 Posts
Do we have any Excel guru's about with time on their hands?

I trying not to resort to VBA to do this, but can't see a way using normal Excel functions.

The problem is quite simple. I have a column of data that will be a mixture of text. I need to total the column in certain circumstances and derive the value from the text, ignoring the H prefix.

E.g.

CX120089
CX128765
H7
H7
H3.5
CX128766

And I want to total up the cells that begin with H.

So in this case the answer should be 17.5.

I should also say that I want to do this without splitting out the column in to other columns. I know I can do it that way, but space is at a premium so I'm trying to do it all in one formula in one column.

Thanks
Dave
the only advice i can give you on this





































































































































take a hammer to it:D:D:D:D:D:D:D
 

·
Wing Commander
Joined
·
14,437 Posts
And I want to total up the cells that begin with H.
Dave
I've been reading this thread with increasing dismay.
I feel like Prisoner Cell Block H

 
1 - 16 of 16 Posts
Top