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 •


#1
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

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


Report •

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

$1.26
$23.69
-$36.24

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.

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


Report •
Related Solutions


Ask Question