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 |
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 |
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 |
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 |
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 |
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. |
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 |
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. |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com