Could On-site Excel Training The Best Solutions For Your Organisation?
Convert Text Formatted Numbers and Dates to Proper Numbers and Dates
One of the problems faced by Excel users is to convert text formatted numbers and dates to proper numbers and dates. This situation is commonly encountered by business users who export data to Excel from their accounting, customer relationship or supply chain systems commonly known as Enterprise Resource Planning (ERP) systems. To convert the data to proper dates and numbers, some excel users resort to manully typing in the numbers all over again. Some discovered that
they will can re-enter the numbers again one by one by pressing the function key F2 followed by the “ENTER” key. Both methods take away their precious time from work. There are actually many methods that could be used to clean up their data in seconds and here are some of them.
Method 1 – convert text formatted numbers and dates without extra space or special characters
This method is useful if the numbers are formatted as text with no “extras” such as spaces or other non-printable characters together with the numbers.
1. Type the number “1” without quotes into any empty cell e.g. G3.
2. Copy the cell (G3) containing the number.
3. Select the range of cells that contains those number formatted as text.
4. Activate the Paste Special function either by selecting Edit -> Paste Special or Point at the highlighted range and click on the righ mouse button, select Paste Special.
4. Activate the Paste Special function either by selecting Edit -> Paste Special or Point at the highlighted range and click on the righ mouse button, select Paste Special.
5. In the pop-up window, make sure that you select Paste ALL and for Operation, select multiply.
6. Click OK and you will immediately notice that the numbers are aligned to the right. When you select the range, you will see the numbers are added up in the toolbar area.
Method 2 – convert text with space or other characters
This method is useful if the numbers contain “extras” such as space or some special characters. Method 1 will not work in this case. You need to use the FIND and REPLACE ALL funtion to replace all the “extras” at one go. As the “extras” get replaced, Excel will check those the cells and present them as numbers instead of text.
In this method, you need to copy one of the “extras” (unseen characters) and use the FIND ALL function to find all of them. Given below is the step by step guide to do this:
1. Select one of the text formatted cells. In the formula bar, copy the “extras”. Assuming that the “extra” is found at the end and cannot be seen with the naked eye, you can highlight the part of the text after the last character in the formula bar. That usually select the unseen character(s). If your “extras” are found in more than one part of the text, you may have to repeat the process to remove these “extras”.
2. Select the range of cells you wish to convert from text to numbers.
3. From the file menu, select EDIT-> REPLACE.
4. Use Ctrl “V” to paste the copied content into the find text box.
5. Leave the replace box blank.
6. Click on the Replace All button and you are done.
Convert Text-formatted numbers to proper numbers.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
