stubborn cell format

[acts2024]

I have two workbooks containing billing data on phone numbers used by
my employer (about 2,600 lines of data in each book). I'm trying to
compare the billing data by copying the relevant rows and columns from
each workbook into a third, new workbook, and then sorting the data by
the phone number.

My problem is that the formatting for the column containing the phone
numbers is different depending upon which original workbook the data
came from. In one workbook, the format of the phone number column is
set to "General" and the "Locked" checkbox is checked under
"Protection". I cannot change the format of that column AT ALL. I have
unchecked the "Locked" checkbox, and have tried changing the format of
those cells to everything possible, and it always remains in the format
of ##########.

I have even tried copying the cells, opening a new workbook and then
"Paste Special-->Values" to try to strip the formatting. No luck. I
also tried copying a cell from the "good" workbook, and then "Paste
Special-->Format" on one of the stubborn cells, but that had no effect
either.

I am not the most seasoned of Excel users, so I have no clue as to why
I cannot change the format of these cells. What setting am I missing?
Are there some steps I can take to "strip" this stubborn formatting out
of these cells?

Thanks!

 

[Pete_UK]

I suspect the phone "numbers" might actually be text values.

You can enter a formula in another unprotected column, such as:

=A1 or =VALUE(A1)

and copy down. You should then be able to change the format for this
column.

Hope this helps.

Pete

 

[EdMac]

Are you sure it's not something as simple as the column width being too
narrow? make it wider and see what happens.

Ed

 

[acts2024]

Pete, that turned out to be the simplest way of dealing with the
problem. I created a new column to the right of the "offending" column,
entered the formula =VALUE(column to the left) and copy/pasted that for
the other 2692 rows. <Voila!>

 

[acts2024]

Ed, I'm sorry, I think my post was misleading by using the # character
to indicate the format since that's what Excel uses when a column of
numbers is too narrow. What I was trying to demonstrate without using
actual numbers was that the field was 10 digits, with no "-" spacers as
in phone numbers. But thanks for the thought!

 

[Pete_UK]

Well, thanks for the feedback.

You could in addition have used <copy> Edit | Paste Special | Values
(check) OK and <Esc> to fix the values of these formulae in your helper
column then deleted the original column.

Pete