I'm having a problem in excel cell formatting

July 27, 2011 at 09:30:20
Specs: Windows XP
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.

See More: Im having a problem in excel cell formatting

Report •

July 27, 2011 at 10:01:36
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


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.

Report •

July 27, 2011 at 13:56:33
I formatted A1:A3 as Text and copied these values into them:


They 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.


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.

Report •

Related Solutions

Ask Question