Forum Discussion
Excel bad format
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.
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'.
- Thanks Hans, much appreciated, will give it a go!
Hans' suggestion is, as always, top class.
I would still use the menu command "Text to Columns" in these cases.
- Thanks dscheikey👍
