Text to columns by any number (not $)?

T

Tammy

I need to find a way to do "text to columns" by any number and not by $. The
$ sign worked except for some of my numbers do not have $ signs.

Any idea's?
 
T

Tammy

Okay - This is what I need to do:

When I do text to columns by tab my column shows as this:

215-900-8907
1000.00

I need to move every second row to the right and move the rows up. Like this:

Column A Column B
215-900-8907 1000.00

I tried a macro but am unsure how to specify the whole document.

Range("B2,B4,B6,B8,B10,B12,B14").Select
Range("B14").Activate
Selection.Insert Shift:=xlToRight
Cells.Select
Cells.EntireColumn.AutoFit
Range("C1:F1").Select
Selection.Delete Shift:=xlUp
End Sub

Any idea's?
 
P

Peo Sjoblom

You can use a formula for that, assume the values start in A2 and you want
A3 to be in B2, A5 in B3. Use 2 columns as help, first in B2 put

=INDEX($A$2:$A$10000,ROW(1:1)*4/2-1)


copy down as long as needed

now in C2 put

=INDEX($A$2:$A$10000,ROW(1:1)*4/2)

copy down by moving the cursor to the lower right corner of C2 and double
click

Select both help column and do edit>copy

then do edit>paste special and select values


Now you can delete column A or better copy the 2 help columns to another
sheet or workbook


That way you would have the original data left



--


Regards,


Peo Sjoblom
 
E

Excel_Learner

Dear Peo,
u are really genius. I salute.

Peo Sjoblom said:
You can use a formula for that, assume the values start in A2 and you want
A3 to be in B2, A5 in B3. Use 2 columns as help, first in B2 put

=INDEX($A$2:$A$10000,ROW(1:1)*4/2-1)


copy down as long as needed

now in C2 put

=INDEX($A$2:$A$10000,ROW(1:1)*4/2)

copy down by moving the cursor to the lower right corner of C2 and double
click

Select both help column and do edit>copy

then do edit>paste special and select values


Now you can delete column A or better copy the 2 help columns to another
sheet or workbook


That way you would have the original data left



--


Regards,


Peo Sjoblom
Click to expand...
 
Top