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
Frank,
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:
=VALUE(LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2))
Format the column as "currency" and you're good to go.
enjoy
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
Frank,
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.
enjoy
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
Frank
