How to create 3 Columns from Data in 1 Column using every 3 Cells to create the 3 Columns?

leeuniverse

New Member
Joined
Mar 30, 2023
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
So, I have one column of data in this format...

URL
USERNAME
PASSWORD
URL
USERNAME
PASSWORD
...etc. etc.

The above repeated over and over again in one Column, and each one is its OWN Row and Cell.
I need to grab all three items creating 3 columns for each item. In other words, url, username, and password will each have its OWN column.
I need to put each group into the same columns so having...

URL / Username / Password... as Columns, putting the above data into each one.

I need a way to make this easy, cause I can manually copy and paste using "Text to Columns" for each one, But I have a few 100 Passwords I need to create which I'll die doing.
I need to do this so I can import all these passwords into a Browser as a .CSV file. My old browser 20 years of pw's doesn't and won't anytime soon have an export function. I was at least able to grab the "text" of my logins putting the info into a single column.

Please help, thank you... I have tried researching this, but I just can't find exactly what I'm needing to do. It's how I figured out how to do the above, but I need a "Formula" or whatever to make this fast.
Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Welcome to the Board!

Try this VBA code:
VBA Code:
Sub MyTransposeData()

    Dim r As Long
    Dim n As Long
    
    Application.ScreenUpdating = False
    
'   Set row to start on
    r = 1
'   Set initial counter value
    n = 1
    
'   Loop through data
    Do
'       Exit if at end of data
        If Cells(r, "A") = "" Then Exit Do
'       Copy data
        Range(Cells(r, "A"), Cells(r + 2, "A")).Copy
'       Transpose and copy
        Range("B" & n).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        Application.CutCopyMode = False
'       Increment values
        n = n + 1
        r = r + 3
    Loop
    
'   Delete column A when done
    Columns("A").Delete Shift:=xlToLeft
        
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Thanks everyone for the input...

This initial solution ended up working and easy, on Office 2021.

Try

=INDEX(range, SEQUENCE(rows, columns))
Where

Range is your 1 column range holding your data

Rows is the number of rows you are expecting in the output. This should simply be number of rows in your input data dividee by 3

Columns is the number of columns you require in your output. This is 3 for you.
This will end up something like

=INDEX(A2:A301, SEQUENCE(100, 3))
Note that is assumes you are using a version of Excel that includes the SEQUENCE function.

Or if you have the newest version of Excel 365

=WRAPROWS(A2:A301, 3)
 
Upvote 0
Thanks everyone for the input...

This initial solution ended up working and easy, on Office 2021.
Did you come up with that yourself, or did you also ask the question on another forum and get the answer there?
 
Upvote 0
If you are posting the same question in multiple places, that is known as "Cross Posting", and we (as well as most other Excel forums) have rules regarding it.
Cross-posting is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too (this explains the issue/concern with it)!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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