– Today we’re going to tackle two annoying Excel problems when working with imported data. Well, not if it’s

imported with power query because you can do the cleaning with that but like if you’re

directly retrieving data via an add-in, for example from SAP or from Oracle. Or even copying and pasting from other

databases directly into Excel. So number one annoying

problem is that your VLOOKUP formulas don’t work. You get an error, why? Well generally the reason is that your numbers that were imported or copied and pasted

from the other system. They aren’t always

recognized properly in Excel. So they’re not recognized as numbers even though they look like numbers. Second problem is when your dates aren’t recognized as dates even though they look like dates. Let’s see how we can fix this. (Upbeat music) Let’s first tackle the

number recognition issue. Sometimes your numbers can have an apostrophe in front. This means that Excel hasn’t actually recognized them as numbers. And you’re gonna notice that

if you go inside the cell and you press enter, the apostrophe is probably

going to disappear Now there could also be cases like the one I have here where the numbers don’t

have an apostrophe in front, it tells me it’s general format and my formula still doesn’t work. So let’s do a test here. I’m gonna type the 1030 code and I’m gonna do a vlookup

to find its description. OK so simple vlookup formula, I’m not really going to explain it. If you’re wondering how it works. I have a video on this so make sure you check the link in the description. We’re gonna look it up in here. We’re gonna return the second column and we want to go with false. Check this out, I get #N/A even though this is 1030 and that’s 1030. Now even if I go to the custom

formatting dialogue box here and say well this is a number. It still doesn’t work, right. I click on here, it says number, it doesn’t work. The way you can get it to work really fast for everything that’s in here is to highlight it first. We can highlight the entire column. Then go to data, text to columns, and just press next, okay

just go next, go next and say finish and check this out. It transformed all of these

values to actual numbers. So when I go back to the home

tab it still shows me general but Excel knows now that

these are real numbers. Okay so make sure you do this if you can’t find a reason why your vlookup formula isn’t working and everything inside the formula has been correctly written. Now let’s jump in to our second problem which is the date problem. And they look like this so it kind of looks like that

our dates are being recognized but if I click on this filter I see that they’re actually

just seen as number filter because Excel is smart. If it recognizes that it’s a date, it gives you a special date filter. If I even try to write a formula, so let’s just try to get

the year out of this number. It doesn’t work. If I try to format this as a date here, let’s do short date, it doesn’t work. Okay, its not recognized as a date. So what you can do here

is, do the same thing. Use text to columns. I’m gonna highlight this. I’m gonna go to data, text to columns. Now here you do have to do something. You can’t just click next, next, next, it’s not going to do anything. I’ll just show you. It’s still the same number. Okay so don’t just go next, next, next. You need to change a setting when you come to the

last option right here. So instead of general,

I’m going to select date and I’m gonna specify

how the date is here. So the first one is not month but it’s year. So it’s year, month, and then day and then I’m gonna say finish. Now it recognized them as real dates so if I click on the filter here I can see date filters are activated. So anytime you run into

problems like this, remember you can use text

to columns to fix it. I hope you enjoyed this video. If you did, give it a thumbs up. And if you want to learn more about Excel, you want to become more advanced in Excel, consider subscribing to my channel. (upbeat music)

Thank you for this wonderful effort sweet queen

Dear leila as usual i am ammazzed by the way u r tackling such a common issue of dates and number format recoganization issue so smartlly 😈😈😈

Finally I know how to fix this 😅 .. I used to copy all number and paste it in Word file then copy and paste it again into excel . Excel starts to recognise it .. you are the best

Can I share this tip as a bonus tip for one of my conference tips? I will give you credit, of course.

Great tip on the Text to Columns. I use Text to Columns for another hack (when I paste a comma delimited value in a cell and it wants to auto-parse them into separate cells.)

Thanks

Teşekkürler Leila.

Super trick Lelia as always, I'm planning to learn Excel VBA, is there anyway to enroll this course which is teach by u?

Owo ,mam this is fantastic . Thanks a lot

I was struggling before to achieve this with a formula. So we put an Excel dump on a second tab and the first tab is reading all the relevant information we want from the second tab with index and match. So without modifying the second tab, still a challenge 🧐

Thank you for the text to column hint

Just beautiful: this one I can consider to explain at work (and I will), just as I did with the unhide column feature (yes that’s the level I’m dealing with). The stuff I’m doing with PQ and PP: not a chance: literally no one at work knows this…

Thank you Leila!

You are gr8, the trick is really helpful

Wow

It’s sad that I got excited seeing this video in my feed.

Wow Leila, what a nice thing you shared, really awesome! Hats off to you, keep posting

=TRIM(text) if VLOOKUP doesn't working

Great tip 👍🏻

Nice video…show some alternative way around of using MAXIFS for office 2013 or 2016 users.

Another tool I use for converting stubborn text is multiply by 1. Copy 1, edit>paste special>multiply

honestly, it was awesome!!! Thank you

And to think I have been multiplying the text numbers by 1

♥️♥️♥️

Thanks a lot. Learnt something new 👍

How to convert dates generated by a computer system as sep-1-2018. ??? I changed it to date format and nothing happens. Thank you

Why are you centering numeric values?

Thanks heaps, Leila! I came cross this problem recently.

Nice trick iam struggling to find solutions for it many times

Thanks… Such kind of errors are frequently happens when I use excel

Brilliant tip👍

What is your favorite function? Mine is indirect()

The best Excel instructor, as always

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

Wiw, thank you, i wax using mid fuction to draw out the date

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!

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 🙂

Simply great. Keep it up Leila 💕💕💕💕💐💐💐💐👍👍👍👍👍👍👍👍👍👍👍

Easy and smart as well

Excellent tip. Thanks

I love your video tutorial. Thank you very much, I can much learn MS Excel and improve my skills.

Excellent…

Always with great solutions! Thank you!

Amazing. Many thanks

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!!

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

Really.. U are showing.. Unknown facts as facts… It's amazing thanks

Great Stuff!

Thank you Leila, as for the comment of Wayne : sometimes you can just multiply by 1 to get a number. If I encounter such a problem , I first use a very simple formula : like A3 = B5 in order to check if the values are realy the same, the result should be TRUE then.

i am working at moment also on a project where i can't implement this solution even that is a nice,easy(i have a number+can be extra caracters but my formulas are filtering out a specic number from this "code" and even if it was only number what i enterred originally the cut out part of that number is not recognized)-multiply x1 in formula puts everything in order

Hi, I have the same issue. I have tried this and my vlook up doesn't work. I'm trying to do arrange my age in an age group however, my age was derived from a formula so I think it's not working because there's a formula in the age category? Help 😕

Kudos, Leila! I lost so many time with this problem while processing data imported from a mainframe. Thank you for this quickie this will help a lot.

Thanks Leila.

So simple and yet so helpful. Thanks!

thanks you so much

Thank you for your clear and precise tutorial

Nice old-school tricks! Text to Columns is definitely a hidden gem in Excel. It can do many magical transformation we may not expect. It is a must-know tool for those who hesitate to move into Power Query. 😛

Btw, for Problem #1, we may wrap the first argument with TRIM to transform the lookup value into text, so that we don't have to touch the data in lookup table.

what a nuisance!

a number that looks like a number should not be imported in excel in some weird format no one is gonna need or use.

Thank you so much Leila, This is greatttttttt. You just don't know how helpful this video for me. I all the time run to this problem when transferring data from access to excel. I never knew how to fix it. Thanks Again.

I ran across your videos only recently and I appreciate your teaching abilities and knowledge.

One thing I do with VLOOKUP is to modify the lookup value because sometimes you don't want to change the array values in the table, or you import new data and it overwrites your text to column changes. I modify the lookup value in the vlookup function.

If I have text as the lookup value and numbers in the array a simple VLOOKUP(lookup_value*1,lookup_array,col_index_num,range_lookup) will work, and if the lookup value is a number but the array contains text I use

VLOOKUP(TEXT(lookup_value,0),lookup_array,col_index_num,range_lookup).

Good teacher

Text to columns is ok when working with a small data set or a quick one off lookup, personally I find it better to correct the data, or allow for cross conversion from number to text

some tips……

Numerical values should align right by default, the centre aligned numbers here should be your first clue that something isn't right…..removing formatting from the column will show you that the numbers are in fact text.

if a VLOOKUIP doesnt work when expected you can use a double negative to force a conversion to a number…..so VLOOKUP(–A1,B:C,2,FALSE) will work when looking up a number stored as text from a numerical column.

Similarly you can use TEXT with an argument of "0" within a VLOOKUP to force conversion from a numerical value to a string…..so VLOOKUP(TEXT(A1,"0"),B:C,2,FALSE) will work when looking up numerical value from a column of numbers stored as text.

Using Excel's built in 'convert to number' function can be painfully slow, insted temporarily insert a column to the right of the data and use a double negative (=–A1) to convert

Hope this helps someone 🙂

Great Tricks. Love them all 🙂

This was so helpful I get this problem from time to time. Thanks so much

Thank you so much … extremely useful.

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

How can filter by several names?

For instance I have a sales table in which there is a column that contains seller's name and a transaction

Also I have a list of names of sellers that I am interested in, but there are a lot of them, so it's time consuming to manually check each and every checkbox with correct name. What is the best way to filter by names? I mean show only that are in the list of names (for instance these names could be represented on a separate worksheet or just in another separate column?

I am sure I lost some brain cells auditing an excel formula, that I know to be correct but it would not work. The data was imported from an accounting software.

I saw it was text so I converted them using =(cell ref)*1. still did not work in the formula. Now I know the fix. Thanks Leila.

Very helpful video

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

Thanks once again for the valuable information, always found u at "rescue"

The first problem just happened to my data again today. Thanks Leila!

Superb!

Thank you so much..You're a gem. Vlookup problem was constantly bugging me while working on the last few days of an important project lately.

Thank youuu!

Thank you

hi

Leila Gharani awesome video with reference to the above video i am facing an issue / problem in excel where in i have 10000 customers and sub borkers data so on one column i have start date of joining of a 10000 subbrokers example 1st brokers doj is 01/11/2011 and todays date is 22/02/2019 so i want a formula in column no 2 having total no of years compeleted by the sub broker till date , column no 3 having total no of months compeleted by the subbroker till date ,column no 4 having total no of days compeleted by the subbroker till date.

Further in column no 5 i want bucketing formula example A)subroker compeleted 1 to 2 experience B) A)subroker compeleted 3 to 4 experience C )subroker compeleted 4 to 6 experience

so excel should show that he falls in bucket no A , b , c or nos 1 or any reference and so on an so fourth

thanks a lot for your kind help your the best …. MY emailid is [email protected]

Leila, you are the best instructor whom I trust and value alot. This particular video really has helped me. I would like to learn more on excel and be an expert so that it could help in my work, so will xelplus assist?

Wow this is so much easier than the way I was doing it! I am a recent subscriber and very happy I subscribed. Great videos Leila. Always right to the point and very easy to follow 🙂

Very good trick

Mam will u help me to how to convert the text value into numbdr value and making sum of the data in the excel sheet

Leila, I love you

it's work very very thanks & go ahead

I'm from Brazil. Gostei do teu canal.

thanks Leila.i'm from Indonesia I like your video very Interesting.God Job..👍

Wow! I love how you're such an Excel enthusiast and so beautiful too. Im a Software Developer working in Finance and use Excel pretty much for all Front Office/Trade/Portfolio analysis. Thank you for all your amazing videos.

I use text to columns all the time. My question is why the h*** doesn’t it work to change format by using the ’format changer’ on the home ribbon?

Гениально!

Brilliant !!!

Can it be done with a full range of dates stored as text?

I was facing vlookup issue from long time ..thank you very much for this

Amazing. I needed this. Thanks

Loads of thanks!

Thanks ☺

love your videos…wish I knew this a long time ago! thank you

Beautiful AND smart…Thanks! I had over 3,000 lines to fix.

you saved my day, thank you!

Can I use pivot table to replicate countifs sumifs formulae having multiple criteria?? Please help in this regard.

Thank you very much maam i have been facing this problem for a long time but i understand after I have seen this video…thanks a million maam

Everytime i had a problem you helped me to get out of it. One such video. 😊😊

The date text data used by you is "shote date" i.e 30112019, Please tell what/how to do it if this text date looks as if LONG DATE i.e 30-NOV-2019 or even longer "30-NOV-2019 9:07:06 PM GMT+00:00

".