Format a table into two columns

Sabakarp

New Member
Joined
Jun 14, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a data table as shown below:

1a6f11k
2b7g12l
3c8h13m
4d9i14n
5e10j15
o​


What I need to do is take all the numbers and but them in one column and all the letter in a adjacent column. As shown below:

1a
2b
3c
4d
5e
......

Any help would be appreciated!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
See if this does what you want. Test with a copy of your workbook.

VBA Code:
Sub Rearrange()
  Dim c As Long
  
  For c = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
    Cells(1, c).Resize(Cells(Rows.Count, c).End(xlUp).Row, 2).Cut Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1)
  Next c
End Sub
 
Upvote 0
Solution
See if this does what you want. Test with a copy of your workbook.

VBA Code:
Sub Rearrange()
  Dim c As Long
 
  For c = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
    Cells(1, c).Resize(Cells(Rows.Count, c).End(xlUp).Row, 2).Cut Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1)
  Next c
End Sub
That works perfectly! Thank you!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top