Split one coloumn into 2 in Excel
Hi,
I have an excel spreadsheet where I have a lot of values in coloumn A. I want to split this into A and B. ( like a snake ) Coloumn A have: 1000, 1001, 1002, 1003 etc I want to have A: 1000, B: 1001, A: 1002, B: 1003 etc Is there an easy way to do this? |
Split one coloumn into 2 in Excel
On 29 Mar 2007 03:53:16 -0700, wrote:
Hi, I have an excel spreadsheet where I have a lot of values in coloumn A. I want to split this into A and B. ( like a snake ) Coloumn A have: 1000, 1001, 1002, 1003 etc I want to have A: 1000, B: 1001, A: 1002, B: 1003 etc Is there an easy way to do this? Assuming your values are all integers, and separated by a ", " (<comma<space), and that the strings are less than 256 characters long, the following should work: Download and install Longre's free (and easily distributable) morefunc.xll add-in from http://xcell05.free.fr Then use the following formula: =REGEX.MID($A$1,"[^, ]+",(ROWS($1:1)-1)*2+COLUMNS($A:A)) Copy/drag across one column. Copy/drag down as far as required. --ron |
Split one coloumn into 2 in Excel
Hi,
Try this Place this in B1 =INDEX(A:A,ROWS($1:1)*2-1) Place this in C1 =INDEX(A:A,ROWS($1:1)*2) Highlight both cells and drag down as far as you need. HTH Martin |
Split one coloumn into 2 in Excel
On 29 Mar, 13:55, "MartinW" wrote:
Hi, Try this Place this in B1 =INDEX(A:A,ROWS($1:1)*2-1) Place this in C1 =INDEX(A:A,ROWS($1:1)*2) Highlight both cells and drag down as far as you need. HTH Martin Thanks for reply but I don't think this will solve it.... In my example I had 1000, 1001, 1002 etc. but further down this can shift to 2005, 2010, 2027 etc This is just numbers that are inserted as text and are not linear. Maybe I should put an exampe with A, B, 1, 2, 3, C, H where I want A- column to have A, 1, 3, H and the other ones in B coloumn |
Split one coloumn into 2 in Excel
Hi,
Did you try it as STATED? It will do exactly as you ask. It doesn't discriminate as to what is in the cell. A1 will go to B1 A2 will go to C1 A3 will go to B2 A4 will go to C2 A5 will go to B3 A6 will go to C3 A7 will go to B4 A8 will go to C4 etc. etc. In your revised example cols. B and C will look like this A B 1 2 3 C H Once you have dragged it past the half way point of your data it will return zero. HTH Martin |
All times are GMT +1. The time now is 11:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com