Find and Replace in number column

Hi,

Has anyone found a way to remove the ' that excel will sometimes place in front of a number? I just recently discovered this was the reason my calculations weren't correct. I've been removing one a time, but that is a huge pain. Would be nice if there was a Find and Replace function.

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

Answers

  • Hi Darla,

    Are you using a formula to do the calculations? If so, you could wrap the VALUE function around the referenced cell so that it takes the value of the cell as numerical and doesn't read the ' . (See here)

    If you're having trouble with this, it would be helpful to see a screen capture of your sheet (but please block out any sensitive data) and also the formula(s) you're using to reference this column.

    Cheers!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you are using a Windows computer, you can use CTRL+F to pull up a Find/Replace box.

    Unfortunately you won't be able to pick up on the ' that is not visible, but what you can do is tell it to find 1 and replace with 1. For whatever reason this will work to remove the apostrophe. Then you would just repeat this for each of the other numbers.

    Find 2 and Replace with 2

    Find 3 and Replace with 3

    so on and so forth.


    One of the plus sides to this is that you do not have to type in every single different number in the column. You only need to grab the first number which means it only needs repeated for 0 - 9.

  • Darla Brown
    Darla Brown Overachievers

    Thanks

    Genevieve, unfortunately this is not in a formula. This is data pulled in from excel. I'm not sure why or how because it's a random cell here and there. Everything is formatted for currency.

    Thanks, Paul. I will try that.

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP