Reformatting an Excel sheet to change monetary signs?

aliasojo

I lost a whole disk worth of data which has now been recovered but my recovered Excel spreadsheets now show $ instead of £.

Is there a quick way to automatically change all $'s to £'s?

If I had to highlight each cell on each sheet I'll be here all day.

Syman

can you use the find/replace function in find/select (binocular icon) in the home tab

aliasojo

Ah, thought you'd cracked it then. But when I try to replace $ with £, I get a message telling me...Microsoft Excel cannot find any data to replace.

No idea why this is, the signs are there, I can see them. :huh:

HoofeHearted

Sounds like you have the cells formatted as currency.
Try highlighting the cells showing the $ symbol and just reformat with £.:j

TonyMMM

Sounds like the global setting of the machine is defaulting to USA.

(Assuming Windows7)...open Control Panel, then Clock Language and Region, Region and Language, Change the date time or number format ...choose Additional Settings and select the currency tab.

Make sure it is set to £.

Then re-open excel ...

aliasojo

HoofeHearted wrote: »

Sounds like you have the cells formatted as currency.
Try highlighting the cells showing the $ symbol and just reformat with £.:j

I've just checked...every cell is formatted as 'custom', this must be something to do with the recovery process. They most definitely weren't formatted this way previously.

TonyMMM wrote: »

Sounds like the global setting of the machine is defaulting to USA.

(Assuming Windows7)...open Control Panel, then Clock Language and Region, Region and Language, Change the date time or number format ...choose Additional Settings and select the currency tab.

Make sure it is set to £.

Then re-open excel ...

No the global setting is UK. The excel sheets were on an external hd that came from my laptop and were recovered and saved to the main pc hd.

I've just realised much more of the formatting has been affected. All the data is there but I've lost the conditional rules I had set up and some cells that were merged no longer are.

Oh well looks like I've got a long day ahead.

Murielson

Do you have an older, archive version where you can check cell settings so you can at least set them back to previous settings?

johnmc

Assuming Office 2010.

Highlight the affcted cells.

Right click and select Format Cells.

In the Category pane select Currency. In the Symbol pane select whichever global currency you fancy.

MoneySavingUser

Murielson wrote: »

Do you have an older, archive version where you can check cell settings so you can at least set them back to previous settings?

If you do you might be able to copy from the old one and then in the new one do Paste Special --> Formats only to copy the formats over (don't think it will work for conditional formatting)

aliasojo

johnmc wrote: »

Assuming Office 2010.

Highlight the affcted cells.

Right click and select Format Cells.

In the Category pane select Currency. In the Symbol pane select whichever global currency you fancy.

That's what I didn't want to have to do, I was looking for a way to automatically convert the whole sheet without having to physically highlight each cell.

MoneySavingUser wrote: »

If you do you might be able to copy from the old one and then in the new one do Paste Special --> Formats only to copy the formats over (don't think it will work for conditional formatting)

Problem sorted now, thank you everyone for all your help.

For some reason, only certain sheets were affected, not all as I had originally thought. I have no idea what made the difference i.e. why some sheets had issues whilst others remained 'as was' iyswim.

Anyway, I did manage to get mostly everything reformatted the way it was originally, largely by copying and using the 'paste special' command as mentioned above. (It did transfer the conditional rules too btw.)

These sheets are now saved in at least 6 different places.

p00hsticks

aliasojo wrote: »

These sheets are now saved in at least 6 different places.

Then make sure you have good version control procedures in place ...