Forum Discussion

Grant de Jongh's avatar
Grant de Jongh
Copper Contributor
Feb 13, 2023

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.
Excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 13, 2023

    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'.

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Feb 13, 2023

    Grant de Jongh 

    Hans' suggestion is, as always, top class.

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

     

Share

Resources