Remove character from a cell

[ashley1984]

Hi all,

I have a large set of data where I need to clear out a full stop (period) "." from specific cells in the data range. Where there are no sales for a customer the data comes back with a "." in the cell against that customer and product.

The above screen-grab is an example of the data. This is just an example, the actual data set is much bigger. I have highlighted the cells in yellow where I need to remove the ".". I have tried several options:

1) Conditional formatting to highlight all cells with "." in yellow and the filter by colour and delete cells - this takes ages!
2) Power Query but I can work out how to replace the cells just with "." - I have tried the replace values option but it does not seem to work.
3) The best solution I have tried is the =SUBSTITUTE function - this appears to work, and actually removes the "." from the yellow cells, but it also removes the comma separating the numbers in the other cells - see screen shot below. I have left in the yellow highlited cells to show the "." has been removed.

Any other ideas on how to do this?

Thanks

 

[Fluff]

If the period is the only thing in the cell, you can use Replace.
Ctrl H, in the Find What box put a period & check "Match entire cell content", Replace all.

 

[ashley1984]

If the period is the only thing in the cell, you can use Replace.
Ctrl H, in the Find What box put a period & check "Match entire cell content", Replace all.

Amazing! Thank you. Such a simple solution. I did try this originally but did not select "Match entire cell content"

 

[Fluff]

You're welcome & thanks for the feedback.