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

**leeuniverse** · 03-30-2023, 05:36 PM

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

**TMS** · 03-30-2023, 06:18 PM

Try:

Formula:

Please Login or Register  to view this content.

**TMS** · 03-30-2023, 06:25 PM

If you don't have LET and/or HSTACK, try:

Formula:

Please Login or Register  to view this content.

**TMS** · 03-30-2023, 06:41 PM

Think you'll have LET but not HSTACK, so:

Formula:

Please Login or Register  to view this content.

It's shorter and you only need to adjust the range in one place.

**FlameRetired** · 03-30-2023, 07:54 PM

And looking ahead should you ever decide to upgrade to Office 365 the WRAPROWS function makes short work of this. The formula would be

Formula:

Please Login or Register  to view this content.

**leeuniverse** · 03-30-2023, 10:08 PM

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)

**TMS** · 03-30-2023, 10:50 PM

What do you mean by "initial solution"? Where did you come by that formula?

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

LinkBack

Thread Tools

Rate This Thread

Display

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

Re: How to create 3 Columns from Data in 1 Column using every 3 Cells to create the 3 Colu

Re: How to create 3 Columns from Data in 1 Column using every 3 Cells to create the 3 Colu

Re: How to create 3 Columns from Data in 1 Column using every 3 Cells to create the 3 Colu

Re: How to create 3 Columns from Data in 1 Column using every 3 Cells to create the 3 Colu

Re: How to create 3 Columns from Data in 1 Column using every 3 Cells to create the 3 Colu

Re: How to create 3 Columns from Data in 1 Column using every 3 Cells to create the 3 Colu

Thread Information

Users Browsing this Thread

Similar Threads

Formula to take data from columns to create full sentence in another column

[SOLVED] Create New Columns On New Sheet Based On Column Data

Create a sepearte column baesd on the data in other columns

Merge two columns to create 1 column with all the data

Need a macro to format data and delete columns and create new column

[SOLVED] an I create a PivotTable with two columns of data for the same Column header?

Create one new column of unique data from two columns

Bookmarks

Bookmarks

Posting Permissions