# Splitting first 3 digits from a 5 digit cell

May 17, 2011 at 10:24:55
Specs: Windows XP
 Need assistance with removing the first 3 numbers from a 5 number cell.ex.Have .....Need..A1........B1..00284...00200346...00300986...01234i used =left(A1,1,2,3)but it removes the "0"s and it gives me 284please help

See More: Splitting first 3 digits from a 5 digit cell

#1
May 17, 2011 at 11:40:33
 Your numbers in column A are being read as TEXT, soFirst format your column B as TEXTThen enter the formula: =LEFT(A1,3)See if that works.MIKEhttp://www.skeptic.com/

Report •

#2
May 17, 2011 at 12:03:33
 it actualy reads as a zip code in 2010

Report •

#3
May 17, 2011 at 12:47:10
 How about:=LEFT(TEXT(A1,"00000"),3)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
May 17, 2011 at 12:54:45
 that gave me 000 in the field.

Report •

#5
May 17, 2011 at 13:30:18
 New 2003 worksheetA1:A4 formatted as Zip CodeCells display:(Copy/Pasted directly from the worksheet)00284003460098601234Entered in B1 and dragged down to B4:=LEFT(TEXT(A1,"00000"),3)B1:B4 display:(Also Copied/Pasted directly from the worksheet.)002003009012I'll try this in 2010 when I get home, but I'll be very surprised if I get different results.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#6
May 17, 2011 at 13:32:46
 Worked for me.With column A formatted as Custom, Special, ZipHow do you have the cells formatted?MIKEhttp://www.skeptic.com/

Report •

#7
May 17, 2011 at 13:41:51
 The file that i copied these from was apperently corrupted. I coppied the entire colum and pasted in a fresh sheet and formated colum B to Special > ZIP CODE and worked perfectly. thanks guys

Report •