I have a huge flat file in which the prices are included down to the
cents, but with no decimal point.  The only options I can find both in
Excel and Miva turn 1000 into $1000.00 instead of $10.00.  How can I
insert a decimal point in all of these without doing it manually?

---

Request for Question Clarification by tar_heel_v-ga on 08 Jan 2003 07:06 PST

So, I want to make sure I understand your list:

1000 is 10.00 or is it $1000
45687 would be 45,687.00 or 456.87

-THV

---

Request for Question Clarification by tar_heel_v-ga on 08 Jan 2003 07:15 PST

If your list of numbers is set up to where the last two are the cents
(1000=10.00, 4537=45.37) I have a work around for Excel 2000 using a
few text formulas that should do the trick

-THV

---

Clarification of Question by dotw-ga on 08 Jan 2003 07:16 PST

We want to turn 1000 into 10.00
-- 45687 into 456.87

---

Request for Question Clarification by tar_heel_v-ga on 08 Jan 2003 07:20 PST

I have a solution for Excel that will do what you need.  Will that work for you?

-THV

---

Clarification of Question by dotw-ga on 08 Jan 2003 07:41 PST

yes - thanks much

dotw..

Thanks for your question.  While this may not be the easiest way to
achieve your desired results, it will work.  This is assuming that
your numbers are in a single column.

In column A, place your numbers from your import
In column B, place the following formula: =LEN(A1)
In column C, place a "$" without the quotation marks
In column D, place the following formula: =LEFT(A1,B1-2)
In column E, place a "." without the quotation marks
In column F, place the following formula: =RIGHT(A1,2)
In column G, place the following formula: =CONCATENATE(C1,D1,E1,F1)

You will need to copy these formats into all the rows you have
numbers.

You can either A) Copy the results from Column G and paste them into a
new column using Edit -> Paste Special -> Values or you can hide the
columns containing the formulas and your original data.

You can see an example of the spreadsheet at
http://www.lucidmatrix.com/uploads/ga139224.xls


I have place three worksheets.  One shows the entire format with all
columns showing.  The second shows if you cut the data from Column G
and paste the values.  The third shows the data with columns hidden.

Thanks again for your question and if you need any additional
clarification, please let me know.

Regards,

-THV

good job THV -- just what I needed