Format Problem - Change Text to Genernal 2

[Hongkongian]

I have user given spreadsheet which defined a text field which contain the contract no. (the contract no. is numeric). When I re-format the column from text to general, I found that it hasn't been changed. But, when I double click the cell, it will convert to "General" format. Does anyone know how to automatic to change all the field format?

 

[Bowers74]

Insert the following code into a standard module in this workbook, select the cells you want to change and run the macro.

Sub Text2General()
Dim sel As Range
Dim c As Range
Set sel = Selection
If TypeName(sel) <> &quot;Range&quot; Then Exit Sub
For Each c In sel
    c.Value = c.Value
    c.NumberFormat = &quot;General&quot;
Next c
End Sub

This code will change all text to a number (if it is a number) and will remove any leading apostrophys " ' " that cause a number to be viewed as text. It also changes the cell's format to General.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

HonhKong,

Think of it this way.

Your Contract Number is NOT a number. You will never do arithmetic on Contract Numbers.

Your Contract Number is a STRING of NUMERIC CHARACTERS. Excel, and every other computer program, manipulates and stores NUMERIC CHARACTERS in a much different way than it manipulates and stores NUMBERS.

Your Contract Number ought to be formatted as TEXT, with a leading apostrophe.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com