Cells in formula sharing digits and letters

[questionforyou]

I have a standard table full of numbers, bottom row has adding formula (example) =H4+D4+B4 . Is there anyway to have characters in the same cell as the numbers without the cells that show totals displaying #VALUE!
Seems cells can only contain the digits, no letters
Thanks for any help

 

[Oaktree]

The complexity of how you do this will depend on what you mean.

Are you saying H4 could contain "some text" and you'd still want your formula to add D4 and B4? Or, are you saying that H4 could contain "number 100 in the middle" and you'd want to include the 100 in your sum?

The former is easy with =SUM(H4,D4,B4).

The latter is complicated... not sure if we need to go there yet.

 

[T. Valko]

I have a standard table full of numbers, bottom row has adding formula (example) =H4+D4+B4 . Is there anyway to have characters in the same cell as the numbers without the cells that show totals displaying #VALUE!
Seems cells can only contain the digits, no letters
Thanks for any help

Post several representative samples of your data so we can see what you mean.

 

[questionforyou]

Ya my explanation was not very clear or simple to understand.
(Example)
A
1 10prs. 10prs.
10prs. 10prs.
10prs. 10prs.

Total =A1+A2+A3+B1+B2+B3

- Now instead of my cell in the bottom (the adding formula) showing 60, its show #VALUE! because I have not only digits in these cells but "prs." and of course if I remove the "prs." from these 6 cells, my bottom cell consisting of the adding formula shows "60".

 

[Haseeb Avarakkan]

Hello,

if you just have "prs." in all these cells, Try

=SUMPRODUCT(SUBSTITUTE(UPPER(A1:B3),"PRS.","")+0)

 

[AMAS]

Just like Hasseb said "f you just have "prs." in all these cells" then you can also try:

=SUM(TRIM(LEFT(A1,2)),TRIM(LEFT(A2,2)),TRIM(LEFT(A3,2)),TRIM(LEFT(B1,2)),TRIM(LEFT(B2,2)),TRIM(LEFT(B3,2)

AMAS

 

[questionforyou]

I tried both formulas, cell still shows #VALUES!
I have "10" and "prs" in the cells that are being used in the addition formula

 

[questionforyou]

Ah appreciate it Amas that one works perfectly, thanks for everyones help

 

[AMAS]

Glad it worked. I was having troubles with my internet and didn't think the post made it online. Now I am glad that it did.

Still learning the tricks of complex formulas and vba so this is a nice boost.

AMAS