Sort by time | Sort by thread (beta)
- Your queue is empty. Add videos to your queue using this button:
or sign in to load a different list.
Loading...Saving...
Loading...Saving...
Thanks Mike. I ran into this problem just the other day and I was highlighting ranges and going to Ctrl 1 and changing the data type to text or general. What a mess. Never thought of changing the formula. This makes it much easier.
krn14242 2 months ago
@krn14242 , actually, I always make sure the data is correct. However, sometimes when you import messy data and there is a lot of it, then you have to use these methods!
ExcelIsFun 2 months ago
Mike, great video, helps a lot thanks. Before starting these lookups I always trim the source data (say all in column A) with a macro but is it possible to make sure that everything is a number in column A by adding 0 or whatever without the use of a helper column?
mysimonhughes 2 months ago
@mysimonhughes , I do not understand your question. Can you restate it?
ExcelIsFun 2 months ago
@ExcelIsFun Mike, I mean is there a way to mutiply a column range (say A2 to A200) by 0 (to convert text to number) without using a helper column?
mysimonhughes 2 months ago
@mysimonhughes , actually there a few ways to do it. You could run Text To Column and that will convert text-numbers to numbers. You could add (not multiple) zero to the whole column by using Paste Special Operations: 1) type zero in some cell, 2) copy that cell with the zero, 3) highlight entire column, 4) Paste Special dialog box, 5) click dialog button for Operation Add", 6) Click OK.
ExcelIsFun 2 months ago
@ExcelIsFun - Thats it, many thanks
mysimonhughes 2 months ago
05:00
When you format the number 2 as tekst (without using the apostrophe) The VLOOKUP function still works. But once you do use the apostaphy to format the number 2 as tekst you cannot get rid of it and the VLOOKUP will fail.
Excel 2010
sftw009 2 months ago
@sftw009 , I can not duplicate what you have said. When I format cell as Text and do vlookup with a number in the 1st column of the lookup table, I get an #N/A error...
ExcelIsFun 2 months ago
@ExcelIsFun
It seems to work perferctly fine for me. Also the other way around; Doing a VLOOKUP for a number while having tekst in the first colomn of the lookup table works fine.
I'm using a Dutch version of Excel 2010, is it possible that that casuses the difference.
sftw009 2 months ago
@sftw009 , you must have a special version of Excel becasue it sure does not work for me.
ExcelIsFun 2 months ago