MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Placement of a decimal, within a text column.

Posted by Frank C on January 24, 2002 8:26 PM

I have a spreadsheet where columns of numbers are
imported from a csv file. 2 columns of these numbers
need to have decimal placed in them to reflect "$$ and
cents" Any information on how this may be accomplished
would be appreciated.

Posted by Bariloche on January 24, 2002 8:53 PM


Assuming that all the numbers have "cents" already (that is, that the number is 1200 rather than 12 if it should be $12.00) then this formula will work:


Format the column as "currency" and you're good to go.


Posted by Frankc on January 25, 2002 12:38 PM

Bariloche - Thankyou for the quick response. I am now receiving an error msg. "#Value". I may be making some sort of "Rookie" mistake. My ? does the "A" represent the column and cell. I did format as currency with 0 decimal in format. Your assumption about the number format is correct. Example 127660 needs to be $1276.60.
Thank you in advance for your help, I definitely appreciate the help in moving foward.

Posted by Bariloche on January 25, 2002 5:11 PM


Yes, A1 is the cell located in the first row, first column. You need to modify the formula to fit your situation. For example, if your data starts in column C, row 2 then you would need to change A1 to C2, and so forth.


Posted by Frankc on January 27, 2002 10:40 AM

" Excel cannot clculate a formula, cell references ithe formul refer to the formula's reult, creating a circulat reference." There is a little blue dot in the cell. I do not presently understand circular references. In additon when I format the cell to currency, I am leaving the decimal placement @ 0. Any suggestions to might having in correcting this would be appreciated.
Thank you