Excel bad format

Copper Contributor
Hi, if I receive a spreadsheet with currency values, spaces, and commas that have been manually typed in, instead of the sender has used the formatting, how can I strip out the typed in £,(space), and comma? I.e. how do I convert a plain text (general format) entry of "£ 10,564.00" to "10564.00". To be clear in the issue is that in the formula bar, the value is "£ 10,564.00" not "10564.00".
Thanks all.
4 Replies

@Grant de Jongh 

It should be sufficient to remove the £, Excel should take care of the rest.

Select the range with currency values.

Make sure that the number format is set to General, Number, Currency or Accounting, NOT to Text.

Press Ctrl+H to activate the Replace dialog.

Enter £ in the 'Find what' box and leave the 'Replace with' box empty.

If necessary, click 'Options >>' and make sure that the check box 'Match entire cell contents' is NOT ticked.

Click 'Replace All'.

@Grant de Jongh 

Hans' suggestion is, as always, top class.

I would still use the menu command "Text to Columns" in these cases.

 

Thanks Hans, much appreciated, will give it a go!
Thanks dscheikey:thumbs_up: