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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives