 # Sum Cell Numbers Which Are Preceded With Text

• Hi,

I have a number of cells which contain information such as 'H8', 'S4' and 'T6', etc, etc.

What I want is a formula which sums just the numerical part of these cells, so would add up the 8 plus 6 plus 4.

I have hundereds of these cells filled out like this. The first character in the cell is always a letter and then the number will either be a singe number (e.g. H8) or decimal (e.g. H7.5).

Thanks,

Mike

• Re: Sum Value Part Of Cell Only

Hello Mike

Your easiest route would be to use a helper column with a formula like:

=MID(A1,2,255)+0

copied down the rows to extract the numeric portion out of the cell and then sum the results of this.

An alternative would be to use a single formula like:

=SUMPRODUCT(MID(A1:A1000,2,255)+0)

which would avoid the use of a helper column.

Richard

• Re: Sum Value Part Of Cell Only

Thanks Richard,

The sumproduct formula works well. How could I alter this so that I don't get a #VALUE! error when the formula covers a range where there might be some blank cells?

Could also do with finding out a way of just adding up the cells which start with a 'H'?

Mike

• Re: Sum Value Part Of Cell Only

Try

=SUM(IF(A1:A1000<>"",MID(A1:A1000,2,255)+0,0))

entered as an array formula

and

=SUM(IF(A1:A1000<>"",IF(LEFT(A1:A1000,1)="H",MID(A1:A1000,2,255)+0,0),0))

Might still be a way via SUMPRODUCT but haven't thought of it yet!

• Re: Sum Value Part Of Cell Only

Great! Thanks Yard!

• Re: Sum Value Part Of Cell Only

Don't mix text and numbers within the same cell. IKf you must have a letter preceding the numbers use a Custom Format like: "H"#.

Or, use the adjacent cell for the letter and then use SUMIF or a PivotTable.

## Participate now!

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