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...002
00346...003
00986...
01234
i used =left(A1,1,2,3)
but it removes the "0"s and it gives me 284

please help


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

Report •

#1
May 17, 2011 at 11:40:33
Your numbers in column A are being read as TEXT, so
First format your column B as TEXT
Then enter the formula: =LEFT(A1,3)

See if that works.

MIKE

http://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

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 worksheet
A1:A4 formatted as Zip Code
Cells display:

(Copy/Pasted directly from the worksheet)

00284
00346
00986
01234


Entered in B1 and dragged down to B4:

=LEFT(TEXT(A1,"00000"),3)

B1:B4 display:

(Also Copied/Pasted directly from the worksheet.)

002
003
009
012

I'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, Zip

How do you have the cells formatted?

MIKE

http://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 •

Ask Question