How to Fix VLOOKUP Not Working | Convert Text to Date? (Excel Text to Columns)

In this video, we cover two common and frustrating Excel issues encountered when working with imported data from external sources like SAP, Oracle, or other databases. The first issue we tackle is the non-recognition of numbers in Excel, even when they visually appear as numbers. This often leads to errors in VLOOKUP formulas. We demonstrate a practical solution to ensure Excel correctly identifies these numbers, involving the use of the 'Text to Columns' feature under the Data tab.

The second problem addressed is the challenge of Excel not recognizing dates correctly, despite them looking like standard date formats. This issue can prevent proper sorting and filtering by dates and cause errors in date-related formulas. Once again, 'Text to Columns' comes to the rescue, allowing us to reformat these incorrectly recognized dates into a format Excel understands.

Throughout the video, we provide clear, step-by-step instructions on how to utilize the 'Text to Columns' tool effectively to solve these common data formatting problems. This method is particularly useful when your imported data doesn't respond to usual formatting changes or formula adjustments.

This tutorial uses Excel's Text to Columns feature to address two common Excel problems.

Problem 1: VLOOKUP formula doesn't work
One common reason for your VLOOKUP formulas or SUMIFS formulas not working is that Excel doesn't recognize the numbers as numbers. It sees them as text. Even if you change the cell custom formatting from General to a Number, your formulas still don't work. This is a common issue when you import data to Excel from SAP or Oracle systems.

Problem 2: Dates are not recognized as dates in Excel
Sometimes imported dates from other systems to Excel are seen as text or number but not as a date. Excel's text to column feature can convert these super fast into dates.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[JonathanReadNELincs]

Probably the best 4 minute and 54 seconds i have ever spent watching a Youtube video. That VLOOKUP issue has driven me crazy for years and years. Thank you Leila!!

[wayneedmondson]

Hi Leila.. thanks for the great tip on Text to Columns. For Problem #1, I have always used the shortcut of doing a Copy blank cell, Paste Special Add or Copy a cell with a 1 in it and Paste Special Multiply over the range with the numbers that need converting. For Problem #2, I've usually used a combination of text formulas.. LEFT/MID/RIGHT to pull the date components and then re-assemble them in a proper date. Your Text to Columns trick does it more quickly and more efficiently and handles both problems with the same logic.. just the additional parameter with date conversions.. Outstanding! You've always got a new magic trick to share. Thanks and thumbs up!

[kangsungho]

15 years of excel using, I haven't seen or heard of anything like this! What a valuable lesson this is!

[howImetyourbrother]

You really have saved me today....I was thinking I needed to re-install my excel to try and fix this problem. That text-to-columns trick resolved it for me. You are really a great digital philanthropist. Thank you

[emmanuelu.]

OMG... I'm gonna cry... This is what I was looking for. My god d**m vlookups were always failing, now I can see the light and solution. Great work, thanks for all your help.

[roderickmose]

Now that's what I call a solution!!

Just one add-on, I would definitely export old numbers to a new column so that the new numbers fall to the right as numbers are supposed to and remove the old alignment formatting. Excellent vid!!

[lephterisp]

A nightmare just became a sweet dream..A very usual problem from me has just gone .Thank you once again.

[excelisfun]

It is amazing how these very old-school tricks are still useful and fun! Thanks for the well made video, Teammate : )

[umerjamil]

searching this solution from (9.am to now 11.29 pm) you made my day Thank you very much

[christianbaert]

I wished I knew these tricks 10 years ago !! I spent so much time trying to understand why imported data was not working properly and doing many manipulations without success. Thanks anyway Leila ! I'm always amaized how much I learn from your videos.

[ismailismaili]

It's just a 4 minutes but it opens a problem of four years thank u so much Ms. Leila

Thanks Leila! Useful little trick. These little short videos are very powerful and effective.

[zoolook]

You just saved my day - That VLOOKUP problem is so annoying (and still is present in XLOOKUP)

[DanielRodriguez-swtn]

I was going crazy trying to do a VLOOKUP with data extracted from SAP. Thank you so much!!! :)

[ashrafelnakesh]

You are my hero in Excel, Greetings from Egypt

[robsonnvula]

Hi Leila, this was time saver!!! Thanks immensely for your work.

[mustafaal-mubarak]

Thank you very much.
For the past two days, I wasn't able to solve this VLOOKUP issue.

[alexkim]

Wow Leila. This is indeed a very quick solution to the annoying problem. My way I used to do is always copy and paste, and select "convert to number", for situation 1, but sadly I'm not able to record this via VBA.

Now you have given me this, I suppose it should be recordable.

Then again for #2, I would split them up using "delimiter" but surely this works much faster!

[sachinrv]

Hi Leila, Once again amazing video. I have 3 approaches : 1) Like you said text to column. The key board short cut is ALT+D+E and then press ENTER 3 times. I always prefer not to use mouse and assume that my computer came without mouse. Trust me key board short cuts are 10 times faster than using mouse. 2) One can use ABSOLUTE (ABS) function to convert the text into whole numbers and then work on VLOOKUP. 3) Using VALUE function which does the same trick. For converting numbers into dates; your approach is the best. But if at all these date numbers are also in text format; then again ALT+D+E and then press ENTER 3 times and then use DATE function in combination with LEFT, MID and RIGHT something like =DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2)). Good to learn things from adorable teacher like you :)

[arobatto]

You are a life saver!! I spent days using other solutions and this did the trick, thank you!!