Text to Currency

[wal50]

I have several files to import. They are .txt files where the dollar amount,
in addition to being text, has an implied decimal point (198756 should be
$1987.86). I can import and format the other fields (dates, etc) using the
wizard. How can the text/implied decimal be handled? Also, how can lead
zeros be retained in a numeric format?
Thanks
wal50

[AlfD]

Hi!

2 things.

The value will have to be changed to make it an explicit dollars/cents decimal value. Divide by 100 first.

Second: use format > cells> numbers > custom and type in $00000.00 as your format. Adjust the number of zeroes to suit your sizes of numbers and preferences. This one would put 2 zeroes in front of 123 dollars: $00123

Alf

[Jim Rech]

To divide a range of numbers by 100, enter 100 in a cell and copy it. Then
select the range of numbers and do an Edit, Paste Special and select Divide
as the operation and click OK. Afterwards press Esc to exit copy mode and
format the numbers as currency if you wish.

True numbers cannot have leading zeros in their cells. You could import the
data as Text however and they will stay as they are.

--
Jim Rech
Excel MVP
"wal50" <[email protected]> wrote in message
news:[email protected]...
|I have several files to import. They are .txt files where the dollar
amount,
| in addition to being text, has an implied decimal point (198756 should be
| $1987.86). I can import and format the other fields (dates, etc) using
the
| wizard. How can the text/implied decimal be handled? Also, how can lead
| zeros be retained in a numeric format?
| Thanks
| wal50

[AlfD]

Jim:

Interesting you should say that. The format I gave above seems quite robust. For example, $012345.67 can appear in the cell: this cell times 2 gives $024691.34 when formatted the same. Is it a number? Like the man said: if it looks like a duck and it quacks like a duck....

Alf