Symbol or text after number to used in sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please could you tell me if it is possible to insert a symbol or a letter
after a number in the same cell and still use the number in that cell in a
sum? For example i want to put 11.5* in B1, 11.5 in b2, 15 in b3 and add the
3 numbers togteher excluding the astreick in b4) Thank you
 
This array formula appears to work

=SUM(--SUBSTITUTE(B11:B3,"*",""))

commit it with Ctrl-Shift-Enter
 
Duke Carey said:
This array formula appears to work

=SUM(--SUBSTITUTE(B11:B3,"*",""))

commit it with Ctrl-Shift-Enter
Click to expand...
....

Or use SUMPRODUCT, e.g.,

=SUMPRODUCT(--SUBSTITUTE(B1:B3,"*",""))

and not have to bother with array entry.
 
Harlan -

There's a long series of posts in an earlier thread "Extracting a numbers
from a text string" where I finally got the answer for the OP, but I'd bet
you could promulgate a far simpler solution. Can you take a look?
 
Another way is to use the custom format feature of Excel.

Format cells - custom

enter

# "*"

That way you can use the sum function without all the other hoopla
 
Back
Top