Find and Replace in number column

Darla Brown

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.

Genevieve P.

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

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

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.