• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Numbers as Text

M

mgtyper

New Member
I often have issues with Numbers formated as text. I work with part numbers that can be all numbers or mixed numbers and characters. when i use Vlookup and one sheet has them as numbers and one sheet has them as text the vlookup shows an error. I have tried to reformat but is very frustrating to get them to match with excel helping or i think helping too much.


Anyone know a way to help?
 
To quickly convert any possible numbers formatted as text to number format.

1. Type the number 1 into a cell (that's not formatted as text)

2. Select the cell, copy

3. Press Ctrl+g, Special, Constants (leave all options checked)

4. Edit, Paste Special. Select Values only, and multiply.

5. Ok out.

6. Done.
 
i need them to be all text. There are part numbers with zeros in the begining or an "E" in the middle. Excel will take the zeros off of the begining and change the PN's with an "E" in the middle to scientific notation. This help from excel ruins the data then my Vlookup comes back with errors.


how do i keep my data pure and still use Excel?
 
I'd first select a blank column somewhere, format the column to Text, and then copy - Paste Special Values only the data into that column. That would cause everything to become Text format.
 
Hi mgtyper,


This might be worth reading if you are importing the data?


http://excel.tips.net/T002426_Avoiding_Scientific_Notation_on_File_Imports.html
 
luke,

I think that will help , thank you. This is something i do everyday and i was hoping for an easy fix. i guess i will just need to make a macro to automate.


Aolchippy,

That is a good article but i usually exprot from Access straight into Excel.

I will think about that when i am inporting from other sources.


Thanks
 
Luke that did not exactly work in this case. I ended up adding an apostrophy before the number and had to double click with the mouse on each cell (170 rows with this problem). After the double click the green corner would show and my Vlookup formula would work in the ohter column.

Anyone please let me know if there is a quicker way.

thanks
 
Hi mgtyper,


Index & Match is more efficient then vlookp, format every thing as text and look at this file:


http://dl.dropbox.com/u/60644346/vlookup_error.xlsx


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top