Reformatting excel data

[Brianfree]

Hi i have some data in a excel document...

xxx-yyy
444-4443-434
444-4443-435
444-4443-436
444-4443-437
xxy-zzt
444-4443-454
444-4443-455
444-4443-456
444-4443-457
444-4443-458

but i need to have it seperated into two columns...

444-4443-434	xxx-yyy
444-4443-435	xxx-yyy
444-4443-436	xxx-yyy
444-4443-437	xxx-yyy
444-4443-454	xxy-zzt
444-4443-455	xxy-zzt
444-4443-456	xxy-zzt
444-4443-457	xxy-zzt
444-4443-458	xxy-zzt

Is there a function that can do this based on a change of a value or somthing in a field?

Many thanks,

Brian

 

[randy700]

I don't know of a function, but this is one way.

1. Add another column to get the length of each field:
=LEN(A1)
2. Filter the spreadsheet on the length column.
3. Cut and paste nto a new column.
4. Repeat steps 3 and 4, using the other length values.


Randy

 

[combo]

Assuming that your data starts in A1:

xxx-yyy          x                      y
444-4443-434     =A2                    =A1
444-4443-435     =IF(LEN(A3)=12,A3,"")  =IF(LEN(A3)=7,A3,D2)
444-4443-436     =IF(LEN(A4)=12,A4,"")  =IF(LEN(A4)=7,A4,D3)
444-4443-437     =IF(LEN(A5)=12,A5,"")  =IF(LEN(A5)=7,A5,D4)
xxy-zzt          =IF(LEN(A6)=12,A6,"")  =IF(LEN(A6)=7,A6,D5)
444-4443-454     =IF(LEN(A7)=12,A7,"")  =IF(LEN(D?(A7)=7,A7,D6)

Copy down formulas in table with headers 'x' and 'y'. Next:
- select 'x',
- choose autofilter,
- filter non-empty 'x',
- select filtered data,
- copy to other place.

combo

 

[combo]

Sorry, the last formula should be: =IF(LEN(A7)=7,A7,D6)

combo

 

[Gruuuu]

Hmm... You could do this:

First, add a row above your data. In Cell B1, enter a '0'

(B2:B[i]n[/i]):
=IF(ISERROR(VALUE(LEFT(A2,1))),MAX(B$1:B1)+1,MIN(B$1:B1)-1)

(D2:D[i]n[/i]):
=D1+1

(E2:E[i]n[/i]):
=E1-1

(G2:H[i]n[/i]):
=IF(ISNA(INDEX($A:$A,MATCH(D2,$B:$B,0))),"",INDEX($A:$A,MATCH(D2,$B:$B,0)))

Example attached below