adding numbers with letters

S

Singha

Could you kindly please tell how I may add number with letters.
For example 29A+30A +19A

Thank you
 
K

Ken Johnson

Singha said:
Could you kindly please tell how I may add number with letters.
For example 29A+30A +19A

Thank you
Click to expand...

Hi Singha,

If 29A is in A1, 30A in A2 and 19A in A3, then the following array
formula returns 78A...

=SUM(VALUE(LEFT(A1:A3,LEN(A1:A3)-1)))&"A"

It is an array formula so you have to enter it by simultaneously
pressing Ctrl + Shift + Enter, other wise the #VALUE error is returned.

Ken Johnson
 
K

Ken Johnson

Singha said:
Could you kindly please tell how I may add number with letters.
For example 29A+30A +19A

Thank you
Click to expand...

Here's a better solution that is NOT an array formula and will only add
the A's, so if the 2nd term was 30B instead of 30A it returns 48A...

=SUMPRODUCT(VALUE(LEFT(A1:A3,LEN(A1:A3)-1)),--(RIGHT(A1:A3,1)="A"))&
"A"

Ken Johnson
 
S

Singha

Thank you Mr. Johnson!
--
Singha


Ken Johnson said:
Hi Singha,

If 29A is in A1, 30A in A2 and 19A in A3, then the following array
formula returns 78A...

=SUM(VALUE(LEFT(A1:A3,LEN(A1:A3)-1)))&"A"

It is an array formula so you have to enter it by simultaneously
pressing Ctrl + Shift + Enter, other wise the #VALUE error is returned.

Ken Johnson
Click to expand...
 
Top