Odd number formatting issue

[cubsguy]

Not just odd numbers, but an odd issue.

24
5
23
6
024
022
024
012
024

I'm importing some numbers in Excel from an export from some mainframe legacy system. The numbers originally come into Excel with no specific format. I change the format to number but the numbers do not change. If i select the cell and click into the number like I'm going to edit it, exit the cell, the number will then change to the format I selected. The first 4 numbers above are how I would like them to display and the last 5 are how they come into Excel. I format the entire column to number but they do not change until I click into the number as if I'm going to edit it.

Hopefully this makes sense, I really don't have time to manually edit 5000 numbers just to get the format to work. I'm using Excel 2003.

Thanks, Brian

 

[boblarson]

This is a quick way to deal with it.

Create an additional temporary column and put in the cell: =B1*1 (B1 being whatever the cell next to the column is then autofill down. Next Copy the column and paste over the old using Paste Values.

 

[shades]

Howdy. The are in fact text, any time they come from databases, regardless of how they are formatted. If you don't need leading 0's, then you can type the number 1 in a blank cell outside the range, then select that cell and copy it. Then select entire range of "numbers" to be converted, and right-click and choose PasteSpecial, and select Value and Format in the top portion, then in the middle right section, choose "Multiply". Click okay. Now they will be true numbers.
________
Nevada dispensary

 

[cubsguy]

Yes, that worked. Thank you!

Is it some kind of bug with Excel that doesn't allow the formatting to change or is it something else?

 

[boblarson]

then in the middle right section, choose "Multiply".

Hey thanks Shades! I just learned something new.

 

[shades]

I picked that up several years ago (I think soon after we moved to XL 2002).

Regarding your approach, I have done that. But now if I had to do that on several sheets, it might be worth it to name a number, Nbr and define it as

=1

Then you could use the formula:

=B1*Nbr


That number can have other uses besides this.
________
Ipad guides

 

[boblarson]

Given the simplicity of your using Multiply in the paste special function and the fact that you can autofill a 1, I think that is the most efficient, probably.

 

[shades]

I agree. The number becomes a handy tool for many uses as a defined name. And with slight modications can be used for number of prints, version control, etc. (primarily in VBA).
________
Top penny stocks