Adding number within a cell of text and numbers.

[used2reg]

Hello

I have a spreadsheet that has a bunch of cells from E14:IT14 that contains various codes and i wish to add the number following a particular code and total it in JE12.

The cells in E14:IT14 will contain various codes. I need a total when UBA shows up (a number will also follow UBA).

Example:

Column A
UBA1 MFR1

Column B
UBA2

Column C
WBA

I would like to have a running total in a cell (in this case 3). UBA will always be the first entry in the cell, if applicable.

thanks

[LarryLegend]

To get a running total, you could put this formula in cell E18 and then copy it across

=IF(LEFT(E14,3)="UBA",MID(E14,4,1)+0,0)+D18

Basically, it looks to see if the first 3 characters are UBA, and if they are, it takes the 4th digit (your number value) and added it to the previous columns total to give your running total.

LL

[LarryLegend]

One quick note: The formula above would assume that the number after UBA is only going to be a single digit. If that's not the case, let me know the largest value you would see.

[Ron Coderre]

This regular formula sums the single digit following "UBA" in E14:I14
=SUMPRODUCT(--MID(E14:I14&"UBA0",FIND("UBA",E14:I14&"UBA")+3,1))

Is that something you can work with?

[used2reg]

Larry;

It would be a single digit

thanks for the quick reply

[used2reg]

Ron;

Thanks for the quick reply.