Cell Formatting or Borders

R

Ryan D

How do I input a 5-digit number into Excel so that they are in separate boxes
on the same line, but I don't have to press "Tab" each time to move to the
next box?

For example what I have right now is the number 53421, with subnumber "5" in
the first cell, subnumber "3" in the second cell, etc. Each time I enter one
of these 5-digit numbers I have to hit tab after each subnumber--which means
hitting tab 5 times for each number. This is a bit inconvenient when
entering many numbers.

It doesn't matter to me if this number is all in the same cell or different
cells. But I want to keep the gridlines so that each subnumber is in it's
own box on the same line. This should be really simple, but I can't figure
it out. Any ideas? Thanks.
 
M

Michael

Assuming your 5-digit number is in A1,
In B1 type =left(a1,1)
In C1 type =Mid(a1,2,1)
In D1 type =Mid(a1,3,1)
in E1 type =Mid(a1,4,1)
In F1 type =Right(a1,1)
Copy down as far as you need. HTH
 
D

Dave Peterson

I think I'd do each 5 digit number in its own cell (especially if they're gonna
be in different rows (same column)).

Then after I was done, I'd separate them into individual cells.

I'd use Data|Text to columns
Fixed width
draw a line after each digit
and finish up.

You could even use formulas if you want (and still enter your 5 digit numbers in
column A):

Put this in B1 and drag to F1:
=MID($A1,COLUMN()-1,1)
then drag down.

If your numbers may have leading 0's, use this formula:
=MID(TEXT($A1,"00000"),COLUMN()-1,1)
 
R

Ryan D

Dave and Mike,

I don't think either of these two solutions will work for me. Let me
clarify. I am creating a form consisting of one column where every time I
enter a new 5-digit number it will automatically separate this 5-digit number
into 5 separate cells (same row) as I type, without having to Tab after each
digit. Then I want to be able to enter new 5-digit numbers within the same
column doing the same thing. Is this possible with your solutions above?
Also, I don't understand Dave's answer where he says to do "mid(...)". What
is mid?

Thanks
 
R

RagDyeR

Both Ryan's and Dave's Text formula suggestions will do *exactly* what you
wish.

Have you tried them?

OR, have you *not* tried them because you don't understand what to do?

Post back with your range locations if you would like a step by step
procedure, with an exact cell referenced formula.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Dave and Mike,

I don't think either of these two solutions will work for me. Let me
clarify. I am creating a form consisting of one column where every time I
enter a new 5-digit number it will automatically separate this 5-digit
number
into 5 separate cells (same row) as I type, without having to Tab after each
digit. Then I want to be able to enter new 5-digit numbers within the same
column doing the same thing. Is this possible with your solutions above?
Also, I don't understand Dave's answer where he says to do "mid(...)". What
is mid?

Thanks
 
R

RagDyeR

I did of course mean *Michael's* and Dave's suggestion!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Both Ryan's and Dave's Text formula suggestions will do *exactly* what you
wish.

Have you tried them?

OR, have you *not* tried them because you don't understand what to do?

Post back with your range locations if you would like a step by step
procedure, with an exact cell referenced formula.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Dave and Mike,

I don't think either of these two solutions will work for me. Let me
clarify. I am creating a form consisting of one column where every time I
enter a new 5-digit number it will automatically separate this 5-digit
number
into 5 separate cells (same row) as I type, without having to Tab after each
digit. Then I want to be able to enter new 5-digit numbers within the same
column doing the same thing. Is this possible with your solutions above?
Also, I don't understand Dave's answer where he says to do "mid(...)". What
is mid?

Thanks
 
R

Ryan D

I haven't tried them because I don't understand how to do it. I gave "54321"
as an example 5-digit number, but of course, it won't be this every time.
Yet Michael's solution seems like it's hard-coded to be "54321" into Excel.
Could you clarify exactly how to implement this? Thanks.

RagDyeR said:
I did of course mean *Michael's* and Dave's suggestion!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Both Ryan's and Dave's Text formula suggestions will do *exactly* what you
wish.

Have you tried them?

OR, have you *not* tried them because you don't understand what to do?

Post back with your range locations if you would like a step by step
procedure, with an exact cell referenced formula.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Dave and Mike,

I don't think either of these two solutions will work for me. Let me
clarify. I am creating a form consisting of one column where every time I
enter a new 5-digit number it will automatically separate this 5-digit
number
into 5 separate cells (same row) as I type, without having to Tab after each
digit. Then I want to be able to enter new 5-digit numbers within the same
column doing the same thing. Is this possible with your solutions above?
Also, I don't understand Dave's answer where he says to do "mid(...)". What
is mid?

Thanks
Click to expand...
 
D

Dave Peterson

Select B1 through F1.
(all 5 cells)

copy this and paste it into the formula bar.
=IF($A1="","",MID(TEXT($A1,"00000"),COLUMN()-1,1))
But hit ctrl-enter instead of just enter
(this fills all 5 cells with the formula)

I modified the formula to not show anything if column A is empty.

Now type your number in A1.

Select B1:F1 (again)
Edit|copy
Select B2:F999 (or where ever you want to stop).
Edit|paste

Type some values in column A to see if you did it ok.


Ryan said:
I haven't tried them because I don't understand how to do it. I gave "54321"
as an example 5-digit number, but of course, it won't be this every time.
Yet Michael's solution seems like it's hard-coded to be "54321" into Excel.
Could you clarify exactly how to implement this? Thanks.
Click to expand...
 
Top