Splitting a cell with a 3 digit number into three cells where the first digit may be 0?

**greenfox74** · 02-02-2016, 01:32 AM

Sorry guys for the trouble. I have been trying and trying to wrap my head around doing this and looked for some time now with nothing figured out. What I'm needing to do is I have a cell that has a 3 digit number in it, sometimes it will have a 0 as the first number. I need to take that three digit number and split it into three cells and keep the 0 in the first cell. Like if I have 097 in A1, I would need 0 in B1, 9 in C1 and 7 in D1. I need them to stay as numeric value so my formulas from another sheet will pick them up.

I know this is something simple and I thought I had a formula for this on one of my discs, but I can not find it after a fresh reinstall.

Thank you all so much!!!

**FDibbins** · 02-02-2016, 01:36 AM

Try this...
B1=--left(a1,1)
C1=--mid(a1,2,1)
D1=--right(a1,1)

**humdingaling** · 02-02-2016, 01:37 AM

how are you managing to keep the 0 in front of 097? by custom number format?

**greenfox74** · 02-02-2016, 01:43 AM

Originally Posted by FDibbins

Try this...
B1=--left(a1,1)
C1=--mid(a1,2,1)
D1=--right(a1,1)

Thanks FD,
I'll give that a try. I've been trying similar but been using MID.

**greenfox74** · 02-02-2016, 01:44 AM

Originally Posted by humdingaling

how are you managing to keep the 0 in front of 097? by custom number format?

Thank you for the reply humdingaling.

Yes. Custom number format of 000.

**FDibbins** · 02-02-2016, 01:46 AM

Originally Posted by greenfox74

Thank you for the reply humdingaling.

Yes. Custom number format of 000.

hmm then my suggestions wont necessarily work, because you could have only a 1 - or - 2 digit number. Will take another look

**MarvinP** · 02-02-2016, 01:49 AM

Put your 3 digit number in A1 then..

In B1 try =INT(A1/100)
In C1 try =INT((A1-100*B1)/10)
In D1 try =MOD(A1,10)

**greenfox74** · 02-02-2016, 01:50 AM

Originally Posted by FDibbins

Try this...
B1=--left(a1,1)
C1=--mid(a1,2,1)
D1=--right(a1,1)

That's close to one I have been trying. It's giving me 977 instead of 097. Closer than I was but still not picking up that 0 for some reason.

**FDibbins** · 02-02-2016, 01:51 AM

B1=IF(LEN(A1)<3,0,--LEFT(A1,1))
C1=IF(LEN(A1)=2,--LEFT(A1,1),IF(LEN(A1)<2,0,--MID(A1,2,1)))
D1=no change

**FDibbins** · 02-02-2016, 01:52 AM

Originally Posted by greenfox74

That's close to one I have been trying. It's giving me 977 instead of 097. Closer than I was but still not picking up that 0 for some reason.

See my post 6

**FDibbins** · 02-02-2016, 01:52 AM

Marvin, nice 1

**humdingaling** · 02-02-2016, 01:58 AM

b1

Formula:

Please Login or Register  to view this content.

c1

Formula:

Please Login or Register  to view this content.

d1

Formula:

Please Login or Register  to view this content.

**greenfox74** · 02-02-2016, 01:58 AM

Originally Posted by MarvinP

Put your 3 digit number in A1 then..

In B1 try =INT(A1/100)
In C1 try =INT((A1-100*B1)/10)
In D1 try =MOD(A1,10)

It keeps logging me off for some reason. I dunno. That one is giving me my 0 and the third digit is showing correctly, but giving me 07 for the second digit. Getting closer though.

**greenfox74** · 02-02-2016, 02:12 AM

Originally Posted by FDibbins

B1=IF(LEN(A1)<3,0,--LEFT(A1,1))
C1=IF(LEN(A1)=2,--LEFT(A1,1),IF(LEN(A1)<2,0,--MID(A1,2,1)))
D1=no change

It keeps kicking me off before I can reply. That got it FD. Thank you very much again!!!

**greenfox74** · 02-02-2016, 02:15 AM

Originally Posted by humdingaling

b1

Formula:

Please Login or Register  to view this content.

c1

Formula:

Please Login or Register  to view this content.

d1

Formula:

Please Login or Register  to view this content.

Thank you very much for the help also humdingaling!!!
That one is giving me my 0 also, but it's putting what isn't a 0 as TEXT for some reason. I think that would work also as far as it picking it up in my other formulas. I'll have to test that part.

**greenfox74** · 02-02-2016, 02:19 AM

A GIANT THANK YOU to all three of you for your help!!! This place has never let me down and is always full of amazing people that don't care to help someone out. I'd be nowhere with my work without you all!!!!!

(Still not sure why it keeps logging me out.) But, that's OK.

**FDibbins** · 02-02-2016, 02:52 AM

If you keep getting logged out, make sure you check the "Remember Me" box

**hemesh** · 02-02-2016, 05:05 AM

another way may be if I am getting question right
assuming your digit is in A1 then in B1
=MID(TEXT($A1,"000"),COLUMN(A1),1)+0 drag to right

Splitting a cell with a 3 digit number into three cells where the first digit may be 0?

LinkBack

Thread Tools

Rate This Thread

Display

Splitting a cell with a 3 digit number into three cells where the first digit may be 0?

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Re: Splitting a cell with a 3 digit number into three cells where the first digit may be 0

Thread Information

Users Browsing this Thread

Similar Threads

Color a single digit in a mult-digit number cell

Convert 3 digit or 4 digit number to time format

[SOLVED] LOTTERY FILTER#4, Find if 1 Digit Sum of 2 Digit or 3 Digit, Single Cell w/ dash

How to add check digit to 6 digit number string...

[SOLVED] Splitting cell text strings at 4-digit number and at space after # symbol

Entering 5 digit number, where each digit creates a specific result based on a list.

[SOLVED] How do I identify the 7th digit in a 13 digit number, then establi

Bookmarks

Bookmarks

Posting Permissions