formatting number as text

[MetLife]

Hi,

I have a table with ~100K rows. One of the columns has numbers formatted as text and numbers. When I sort the column and see the "green triangle" on the ones which are formatted as text, and I highlight->right click and choose "format as number". Nothing happens?

I have to click on the "exclamation point" and choose "convert to number". Why doesn't formatting as number work?

The reason I ask is I want to highlight all 100K rows in the table and just format as number. I can't sort and format manually that takes too long and we have a few of these files.

Thanks,

 

[Fluff]

Why doesn't formatting as number work?

That just change the way the cell is displayed, it does not change the actual value.

Select the column & with text to columns on the data tab, delimited, next, clear all checkboxes, Finish. & it should be done.

 

[MetLife]

text to columns -> deliminated -> clear all checkboxes doesn't fix it

the values are already saved in an excel workbook. The issue is we get this from another group - we don't have access to the original file

 

[Fluff]

In that case there is something else wrong with the data. Can you post a sample using the XL2BB add-in

 

[MetLife]

In that case there is something else wrong with the data. Can you post a sample using the XL2BB add-in

I have to get this add-in installed by the IT team.

 

[Fluff]

Ok