How to strip "label" formatting from a number?

Posted by Julie Jevremov on October 17, 2001 1:43 PM

Is there an easy way to strip the obvious "label formatting" that appears to be associated with a column of employee numbers that I have, they are nine digits - often times the first three digits are zeros? I pasted an employee # field into my spreadsheet and want to use it in "Vlookup" but it won't work because the employee # field is appearing as a label? If I type in the same number over the label, the Vlookup formula works?

I have tried to copy these "labels" into Notepad and pasting them back into my spreadsheet which does strip them of the "labelness" but blank cells in between rows are causing different problems there when I paste them back and I was hoping for a way to "strip" within Excel.

Posted by IML on October 17, 2001 1:48 PM

You could highlight the data. Go to data, text to columns and hit finish
Alternively, you could turn your look up number into text. For instance, if you are looking up a number in a1, you could change your formula to
vlookup(RIGHT("00000000"&A1,9),table,2,false)
for example

Posted by IML on October 17, 2001 1:51 PM

vlookup(TEXT(A1,"000000000"),table,2,false)

may be a little cleaner.

Posted by Julie on October 17, 2001 1:54 PM

You Rock! Thanks

Posted by Confused in the UK on October 17, 2001 11:23 PM

IML - Why does Julie call you a Rock??

Posted by IML on October 19, 2001 6:01 AM

I'm assuming rock was used a verb, but who knows! (nt)