Certain cells contain both numbers and text.How the numbers can be filtered from the mix for totaling purpose?

Question

I have to calculate the total by applying the 'SUM' formula.This is possible only when text is removed from the cell or cells.(In this case certain currency symbol is associated with the number in each and every cell after the data is exported from 'Microsoft great planes'.Please note that there are multiple cells the verticla format and each cell contains both text and numbers

Answer 1

YES!

Answer 2

you must have syntax correct. you start calculation with =(equal sign, any text is applicable to the formula otherwise you will see an error message. =sum(A21:cc45), this will add from A21 cell to CC45 cell. You can use the Formula option in Excel, as soon as you type =, a prompt appears at top left of your screen. You can click on arrow head and you can select formula you want and it can prompt for ranges or values. You can then go to Help and type eg SUMIF, and it will show you the format.

Answer 3

Assuming that you cannot change the formatting from currency to numeric and that your data was entered as text '$1234.00 with a leading apostrophy then you can use the following formla in a cell to strip off the leading dollar sign

=VALUE(RIGHT(A1,LEN(A1)-1))

This assumes that the $ is the left most character and you really are dealing with pure text.

If it is a formatting issue then $1234.00 with the above formula will return 234.00 stripping off the left most digit in which case you reformat your cells to numeric.

Answer 4

You need to build a small macro for this... with sum i don't think you can (it's quite "limited" when talking of a little more "complex" string operation).

Answer 5

As far as I know you can't.