Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Split a number from one cell to separate cells for each

I want to enter a number in one cell and then in different cells have each
number appear. For example, I type 12345 in one cell and then have 1 in a
cell, 2 in a cell, 3 in a cell, 4 in a cell, and 5 in a cell. Is there a
formula to do this?

--

Rob
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Split a number from one cell to separate cells for each

Try this:

A1 = 12345

Enter this formula in B1 and copy across until you get blanks:

=IF(COLUMNS($B1:B1)<=LEN($A1),--MID($A1,COLUMNS($B1:B1),1),"")

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I want to enter a number in one cell and then in different cells have each
number appear. For example, I type 12345 in one cell and then have 1 in a
cell, 2 in a cell, 3 in a cell, 4 in a cell, and 5 in a cell. Is there a
formula to do this?

--

Rob



  #3   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Split a number from one cell to separate cells for each

That was what I tried intially, but I got the following:

12345
1
12
123
1234
12345

I want the following results:
12345
1
2
3
4
5

Does this explain what I am looking for a little better?
--

Rob


"T. Valko" wrote:

Try this:

A1 = 12345

Enter this formula in B1 and copy across until you get blanks:

=IF(COLUMNS($B1:B1)<=LEN($A1),--MID($A1,COLUMNS($B1:B1),1),"")

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I want to enter a number in one cell and then in different cells have each
number appear. For example, I type 12345 in one cell and then have 1 in a
cell, 2 in a cell, 3 in a cell, 4 in a cell, and 5 in a cell. Is there a
formula to do this?

--

Rob




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Split a number from one cell to separate cells for each

Ok...

A1 = 12345

Enter this formula in A2 and copy down until you get blanks:

=IF(ROWS(A$2:A2)<=LEN(A$1),--MID(A$1,ROWS(A$2:A2),1),"")


--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
That was what I tried intially, but I got the following:

12345
1
12
123
1234
12345

I want the following results:
12345
1
2
3
4
5

Does this explain what I am looking for a little better?
--

Rob


"T. Valko" wrote:

Try this:

A1 = 12345

Enter this formula in B1 and copy across until you get blanks:

=IF(COLUMNS($B1:B1)<=LEN($A1),--MID($A1,COLUMNS($B1:B1),1),"")

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I want to enter a number in one cell and then in different cells have
each
number appear. For example, I type 12345 in one cell and then have 1
in a
cell, 2 in a cell, 3 in a cell, 4 in a cell, and 5 in a cell. Is there
a
formula to do this?

--

Rob






  #5   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Split a number from one cell to separate cells for each

T: That worked, sort of. What if the number I am using starts with a 0?
--

Rob


"T. Valko" wrote:

Ok...

A1 = 12345

Enter this formula in A2 and copy down until you get blanks:

=IF(ROWS(A$2:A2)<=LEN(A$1),--MID(A$1,ROWS(A$2:A2),1),"")


--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
That was what I tried intially, but I got the following:

12345
1
12
123
1234
12345

I want the following results:
12345
1
2
3
4
5

Does this explain what I am looking for a little better?
--

Rob


"T. Valko" wrote:

Try this:

A1 = 12345

Enter this formula in B1 and copy across until you get blanks:

=IF(COLUMNS($B1:B1)<=LEN($A1),--MID($A1,COLUMNS($B1:B1),1),"")

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I want to enter a number in one cell and then in different cells have
each
number appear. For example, I type 12345 in one cell and then have 1
in a
cell, 2 in a cell, 3 in a cell, 4 in a cell, and 5 in a cell. Is there
a
formula to do this?

--

Rob








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,388
Default Split a number from one cell to separate cells for each

Hi, Jumping in,
I didn't know the Row function could be used to count rows. I thought it
just returned a row number. I like how you use that to get an incrementing
number, regardless of where you are on the sheet. Thanks. Always learning.
Regards - Dave.
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Split a number from one cell to separate cells for each

Excel doesn't recognize leading 0s in front of numbers as part of that
number. If you enter in a cell 0012345, as soon as you hit enter Excel
strips off the leading 0s. The only way to have leading 0s *display* and the
number to still remain a number is to use a custom number format like
0000000. However, the leading 0s are *displayed* but are not part of the
true value of the cell.

If you format A1 as Custom 0000000 and enter 0012345 the *true* underlying
value of that cell is 12345. So, any formula referencing that cell "sees"
only the true underlying value of 12345.

You can get leading 0s to be recognized *if* you treat the entry as a *TEXT*
value. You can do this 2 ways. Precede the entry with an apostrophe like
this: '0012345. The apostrophe will not be displayed in the cell. Or, you
can pre-format the cell as TEXT.

So, if the entry is treated as TEXT and is in the form 0012345 then the
formula I suggested will work and it will convert the TEXT numbers to
numeric numbers.

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
T: That worked, sort of. What if the number I am using starts with a 0?
--

Rob


"T. Valko" wrote:

Ok...

A1 = 12345

Enter this formula in A2 and copy down until you get blanks:

=IF(ROWS(A$2:A2)<=LEN(A$1),--MID(A$1,ROWS(A$2:A2),1),"")


--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
That was what I tried intially, but I got the following:

12345
1
12
123
1234
12345

I want the following results:
12345
1
2
3
4
5

Does this explain what I am looking for a little better?
--

Rob


"T. Valko" wrote:

Try this:

A1 = 12345

Enter this formula in B1 and copy across until you get blanks:

=IF(COLUMNS($B1:B1)<=LEN($A1),--MID($A1,COLUMNS($B1:B1),1),"")

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I want to enter a number in one cell and then in different cells have
each
number appear. For example, I type 12345 in one cell and then have
1
in a
cell, 2 in a cell, 3 in a cell, 4 in a cell, and 5 in a cell. Is
there
a
formula to do this?

--

Rob








  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Split a number from one cell to separate cells for each

I didn't know the Row function could be used to count rows

I understand what you mean but *technically* I used the ROWS function.
There's a difference between the ROW function and the ROWS function.

The ROW function returns an *array* of numbers based on the row references:

ROW(A10:A15)

Returns the vertical array: 10;11;12;13;14;15

ROWS returns a single value that is the count based on the row references:

ROWS(A10:A15) = 6

The same thing applies to the COLUMN and the COLUMNS functions.

COLUMN(A1:E1)

Returns the horizontal array: 1,2,3,4,5

COLUMNS(A1:E1) = 5

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi, Jumping in,
I didn't know the Row function could be used to count rows. I thought it
just returned a row number. I like how you use that to get an incrementing
number, regardless of where you are on the sheet. Thanks. Always learning.
Regards - Dave.



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,388
Default Split a number from one cell to separate cells for each

Thanks Biff. After I wrote to you, I went off and checked the help and
discovered the 2 functions which I thought was only 1. I think I'd noticed
them being used before, but never really paid enough attention.
Obrigado - Dave.
Kiwi in Brazil
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Split a number from one cell to separate cells for each

You're welcome!

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Thanks Biff. After I wrote to you, I went off and checked the help and
discovered the 2 functions which I thought was only 1. I think I'd noticed
them being used before, but never really paid enough attention.
Obrigado - Dave.
Kiwi in Brazil





  #11   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Split a number from one cell to separate cells for each

Thank you, that worked perfectly.
--

Rob


"T. Valko" wrote:

Excel doesn't recognize leading 0s in front of numbers as part of that
number. If you enter in a cell 0012345, as soon as you hit enter Excel
strips off the leading 0s. The only way to have leading 0s *display* and the
number to still remain a number is to use a custom number format like
0000000. However, the leading 0s are *displayed* but are not part of the
true value of the cell.

If you format A1 as Custom 0000000 and enter 0012345 the *true* underlying
value of that cell is 12345. So, any formula referencing that cell "sees"
only the true underlying value of 12345.

You can get leading 0s to be recognized *if* you treat the entry as a *TEXT*
value. You can do this 2 ways. Precede the entry with an apostrophe like
this: '0012345. The apostrophe will not be displayed in the cell. Or, you
can pre-format the cell as TEXT.

So, if the entry is treated as TEXT and is in the form 0012345 then the
formula I suggested will work and it will convert the TEXT numbers to
numeric numbers.

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
T: That worked, sort of. What if the number I am using starts with a 0?
--

Rob


"T. Valko" wrote:

Ok...

A1 = 12345

Enter this formula in A2 and copy down until you get blanks:

=IF(ROWS(A$2:A2)<=LEN(A$1),--MID(A$1,ROWS(A$2:A2),1),"")


--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
That was what I tried intially, but I got the following:

12345
1
12
123
1234
12345

I want the following results:
12345
1
2
3
4
5

Does this explain what I am looking for a little better?
--

Rob


"T. Valko" wrote:

Try this:

A1 = 12345

Enter this formula in B1 and copy across until you get blanks:

=IF(COLUMNS($B1:B1)<=LEN($A1),--MID($A1,COLUMNS($B1:B1),1),"")

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I want to enter a number in one cell and then in different cells have
each
number appear. For example, I type 12345 in one cell and then have
1
in a
cell, 2 in a cell, 3 in a cell, 4 in a cell, and 5 in a cell. Is
there
a
formula to do this?

--

Rob









  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Split a number from one cell to separate cells for each

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Thank you, that worked perfectly.
--

Rob


"T. Valko" wrote:

Excel doesn't recognize leading 0s in front of numbers as part of that
number. If you enter in a cell 0012345, as soon as you hit enter Excel
strips off the leading 0s. The only way to have leading 0s *display* and
the
number to still remain a number is to use a custom number format like
0000000. However, the leading 0s are *displayed* but are not part of the
true value of the cell.

If you format A1 as Custom 0000000 and enter 0012345 the *true*
underlying
value of that cell is 12345. So, any formula referencing that cell "sees"
only the true underlying value of 12345.

You can get leading 0s to be recognized *if* you treat the entry as a
*TEXT*
value. You can do this 2 ways. Precede the entry with an apostrophe like
this: '0012345. The apostrophe will not be displayed in the cell. Or, you
can pre-format the cell as TEXT.

So, if the entry is treated as TEXT and is in the form 0012345 then the
formula I suggested will work and it will convert the TEXT numbers to
numeric numbers.

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
T: That worked, sort of. What if the number I am using starts with a
0?
--

Rob


"T. Valko" wrote:

Ok...

A1 = 12345

Enter this formula in A2 and copy down until you get blanks:

=IF(ROWS(A$2:A2)<=LEN(A$1),--MID(A$1,ROWS(A$2:A2),1),"")


--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
That was what I tried intially, but I got the following:

12345
1
12
123
1234
12345

I want the following results:
12345
1
2
3
4
5

Does this explain what I am looking for a little better?
--

Rob


"T. Valko" wrote:

Try this:

A1 = 12345

Enter this formula in B1 and copy across until you get blanks:

=IF(COLUMNS($B1:B1)<=LEN($A1),--MID($A1,COLUMNS($B1:B1),1),"")

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I want to enter a number in one cell and then in different cells
have
each
number appear. For example, I type 12345 in one cell and then
have
1
in a
cell, 2 in a cell, 3 in a cell, 4 in a cell, and 5 in a cell. Is
there
a
formula to do this?

--

Rob











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i split a cell that contains dd/mm/yy into a 3 separate cel Eva Excel Discussion (Misc queries) 2 October 2nd 07 07:35 PM
separate a text cell to many number cells Khoshravan[_2_] Excel Worksheet Functions 1 July 19th 07 11:22 PM
split text into separate cells jtaiariol Excel Worksheet Functions 7 May 7th 07 09:32 PM
Split date in cell into 2 separate cells Jambruins Excel Discussion (Misc queries) 1 August 9th 06 06:16 PM
Split data from one cell to two separate cells Michele Excel Worksheet Functions 2 October 25th 05 09:27 PM


All times are GMT +1. The time now is 04:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"