You may already be familiar with how you can use the Text to Columns function to split out data into multiple columns (e.g. comma separated data). However did you know you can use it to convert and clean data?
Sometimes your VLOOKUP or formula will not work on data that actually looks perfectly fine. It may be that Excel is not recognising it as a number or text accordingly (test it using ISNUMBER or ISTEXT functions). In the below example I’ve tested using an ISNUMBER function that my data isn’t being recognised as a number.
A tip for overcoming this, rather than going into every single individual cell and resolving formats, or using the Convert Text to Number option in Excel (which can take an age if you have a large dataset). Instead use Text-to Columns.
Below we highlight the entire of column A and go to the Data toolbar at the top and Text-To-Columns
Then in the Text-to-Columns pop up window, leave it on the default settings as below and immediately hit “Finish” without going through the Next options
Then immediately your data suddenly is realigned and the ISNUMBER function is suddenly recognising it as a number as desired.
This may or may not always resolve your issue, but I have found in most cases this is exactly what I need and is much faster to use the Excel’s “Convert data stored and text to number” function which has had me staring at an Excel thinking it over for a long long time and resulted in my ending task and restarting the whole thing! Don’t waste your time with it. Just use Text-to-Columns – it’s magical! 🙂
