Data was sent to me in excel 2003. I have 2007. a column contains ex: $1.26, $23.69, $19.49, -$36.24, -$0.37, and so on. I need to sum numbers in column, but sum won't work on whatever format these cells have. It shows up as a GENERAL format. I have re formatted these cells every which way I can think of. I have used TRIM, I have used CLEAN, I have Pasted Special with MULTIPLY x1, I have used TEXT TO COLUMNS, I have removed the"$" symbol with REPLACE function. and I have used just about every combination of these functions, and others, I can come up with. There is no drop down showing numbers as text. The only way I can get SUM function to work is to re-enter each cell manually. The size of the file prohibits me from doing that. Any new ideas to try would be greatly appreciated.

Lets assume everything is coming in column A, did you try combininng the above things you mentioned? or did you start fresh between each attemtp Try the following in B1

=trim(substitute(A1,"$",""))*1

Then see if sum of B column work.

It sounded like you tried all the standard stuff.

Oh and are you working in the workbook you received, or have you tried copying the data in to a new workbook? I think I would try paste special with the value option selected.

I formatted A1:A3 as Text and copied these values into them: $1.26

$23.69

-$36.24They were left justified and summed to 0 as expected from Text strings.

I then put this formula in B1 and dragged it down to B3.

=IF(LEFT(A1,1)="-","-"&MID(A1,FIND("$",A1)+1,FIND(".",A1)-FIND("$",A1))&RIGHT(A1,2),MID(A1,FIND("$",A1)+1,FIND(".",A1)-FIND("$",A1))&RIGHT(A1,2))*1

This resulted in 3 numbers that summed to -11.29.

Formatting B1:B3 as currency added the $.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll