Import Text into Excel - Can't See Format Change 1

[Therber]

After importing a text file into excel, I need to often change the format (General to Number or Text). When changing the format, the change is not seen until selecting a cell by double-clicking and then selecting a different cell. Is there a way to select a column and apply the same action as double-clicking each cell?

Thank You

 

[vbMax]

Here is what you can do. For my example, assume the column to be
converted is column A...

1. Format columns A and B as desired (numeric or text).
2. Place the following formula in cell B1...=value(A1)
3. Copy cell B1 into the other needed cells in column B (drag
the fill handle).
4. Highlight column B and copy it (control-C).
5. Highlight column A and do "Paste Special" - Values.
6. Delete column B.

If you need to do this a lot, you can write a macro to automate
this entire process. I hope this helps you.

vbMax

 

[Dreamboat]

If that doesn't work, do this PRIOR to changing the format of the columns:

Put a 1 in any blank cell. Now copy the cell with the 1 in it. Select all the "bad" cells. Hit Edit-Paste Special and choose Multiply. This multiplies the values by 1, which forces Excel to see numbers as numbers, even when it seems to refuse to do so. It's commonly occurs when getting data from some sort of external database (mainframe).

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[dawnd3]

I found this by accident, but it totally solved a problem I was having the other day and couldn't figure out. I was trying to sort a field of numbers (which were imported as text) and it would not sort numberically. Thanks!!!! Dawn