Rich needs to VLOOKUP into data with " Total" after the tracking number.
When he tries to Replace " Total" with "", Excel converts the tracking numbers to scientific notation. When he tries to convert everything to numbers and I lose the last several digits when they change to 0000.
In today's video, a new option in Microsoft 365 beta to prevent the conversion to scientific notation. And then a way to hack the VLOOKUP formula.
Table of Contents
(0:00) Copy subtotals in Excel
(0:36) Replace in Excel converts to Scientific Notation
(0:54) Long numbers in Excel last digits change to zero
(1:09) Beta option to prevent Data Conversions in Excel
(1:49) Hacking VLOOKUP to add Total for subtotal rows
When he tries to Replace " Total" with "", Excel converts the tracking numbers to scientific notation. When he tries to convert everything to numbers and I lose the last several digits when they change to 0000.
In today's video, a new option in Microsoft 365 beta to prevent the conversion to scientific notation. And then a way to hack the VLOOKUP formula.
Table of Contents
(0:00) Copy subtotals in Excel
(0:36) Replace in Excel converts to Scientific Notation
(0:54) Long numbers in Excel last digits change to zero
(1:09) Beta option to prevent Data Conversions in Excel
(1:49) Hacking VLOOKUP to add Total for subtotal rows
Transcript of the video:
Today's question, replace is breaking the VLOOKUP. Rich has data with sub totals.
Copies that subtotal data.
So Ctrl + Shift + Down, Ctrl + Shift + Right, and then Alt + ; to select just the cells we can see.
Control + C to copy, and he copies that, and we'll copy that here. He has some VLOOKUPS into that data.
The VLOOKUPS are not including the word total, right?
So Rich says he tries to replace total with "" and it is maddening because he gets scientific notation. I'll choose all of this.
Control H for replace, type the word space, total there, replace with nothing, replace all, all done, we made 180 replacements, but they converted everything to scientific notation.
Choose that column, and change to number, zero decimal places.
And you see that everything is ending in four zeros because Excel can only have 15 digits of precision.
All right, well, there is good news.
If you have Microsoft 365, and you're on the Insider's Fast BETA program, you can go to file, options, advanced, scroll down here to this automatic data conversion, uncheck the first four items.
I didn't realize it.
I knew that this handled incoming CSV, but it also apparently works with find and replace.
So when we do replace all, it gets rather with word total, but it leaves this text, and our VLOOKUPS over here start working.
Now, the hassle is a lot of companies aren't going to allow you to be on the BETA.
So until this rolls out broadly.
Or if you're stuck at back in Office 2016, or Office 2019.
My easy solution here is when we LOOKUP K3, let's just LOOKUP K3, ampersand, quote, space, total, like that, and that will allow it to work right, so rather than touching this, which is going to cause all kinds of problems.
Then we'll just change the VLOOKUP to include the word total.
All right.
So good news, Microsoft 365 is going to fix this eventually, but until you get there, this little hack here with adding the word total in.
I want to thank Rich for sending that question in. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Copies that subtotal data.
So Ctrl + Shift + Down, Ctrl + Shift + Right, and then Alt + ; to select just the cells we can see.
Control + C to copy, and he copies that, and we'll copy that here. He has some VLOOKUPS into that data.
The VLOOKUPS are not including the word total, right?
So Rich says he tries to replace total with "" and it is maddening because he gets scientific notation. I'll choose all of this.
Control H for replace, type the word space, total there, replace with nothing, replace all, all done, we made 180 replacements, but they converted everything to scientific notation.
Choose that column, and change to number, zero decimal places.
And you see that everything is ending in four zeros because Excel can only have 15 digits of precision.
All right, well, there is good news.
If you have Microsoft 365, and you're on the Insider's Fast BETA program, you can go to file, options, advanced, scroll down here to this automatic data conversion, uncheck the first four items.
I didn't realize it.
I knew that this handled incoming CSV, but it also apparently works with find and replace.
So when we do replace all, it gets rather with word total, but it leaves this text, and our VLOOKUPS over here start working.
Now, the hassle is a lot of companies aren't going to allow you to be on the BETA.
So until this rolls out broadly.
Or if you're stuck at back in Office 2016, or Office 2019.
My easy solution here is when we LOOKUP K3, let's just LOOKUP K3, ampersand, quote, space, total, like that, and that will allow it to work right, so rather than touching this, which is going to cause all kinds of problems.
Then we'll just change the VLOOKUP to include the word total.
All right.
So good news, Microsoft 365 is going to fix this eventually, but until you get there, this little hack here with adding the word total in.
I want to thank Rich for sending that question in. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
