Excel - fixing formatting

S

sos-DC

I have a spreadsheet that is
Columns run A-AJ
Rows run 1 through 5136 (yes, five thousand + rows)

Someone else created it (database) and EVERY cell begins with what looks
like an apostrophe, but I cannot do a global search/replace to get rid of
them as I ususally do. Deleting the apostrophe cell by cell is NOT an option.

All the cells also have a small green triangle in the upper left-hand
corner. I am trying to change the phone number format from

0000000000

to

000-000-0000

I did a format change, but it will not show correctly unless I can remove
the apostrophes.
 
S

ScottL

Select the cells with the apostrophes and when the error checking box appears
(the reason for the green triangle) click on it and select "Convert to
Number". Then change the format of the cells to your custom format.
 
B

bj

in cell AK1 enter a 1
copy AK1
select A1:AJ5136
paste special-mutitply
with A1:Aj5136 selected
format>cell>number>special>telephone number
 
S

sos-DC

Thank you "bj"

This is perfect.


bj said:
in cell AK1 enter a 1
copy AK1
select A1:AJ5136
paste special-mutitply
with A1:Aj5136 selected
format>cell>number>special>telephone number
 
S

sos-DC

Thank you Scott. That works well.

I've seen the "box" before and did not ever click it so was not aware of
that method.

Good to learn new things.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top