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?

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

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

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

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