Notepad++ or Excel: transpose two columns, batch for multiple files

Question

I have multiple .txt files in the form of

Name1|number1
Name2|number2
Name3|number3
(...)

And I would need to transpose them to look like:

Name1|Name2|Name3|(...)
Number1|Number2|Number3|(...)

This is easy enough to do for a single file in excel, but I would need a way to do this in multiple files in a folder. Since Notepad++ allows finding and replacing for multiple files, I would like to know if there is a way to achieve this somewhat automatically.

While all files have the same pattern (two columns), not all of them have the same number of rows.

I have tried the following aproaches using Notepad++:

• Replacing | for a carriage return to have everything in a single column, in hopes to somehow group all numbers, move them to the bottom and reorganize.
• Repeat the whole text below using a symbol to separate both copies (find [\s\S]*.*, replace with \0\n%\n\0), in an attempt to delete all numbers above and all names| below to finally reorganize. Also got stuck in the middle.

As you can see, my Regex knowledge is extremely limted, even using Regex101. I don't know if this is easy or hard to achieve, I simply cannot find the solution by myself after several hours.

What can I do?

Thanks for your patience in advance.

Answer 1

Thanks for the answer. I thought about macros, but all my searches ended up mentioning Power Query. It is a business oriented add-in, and I would rarely need it, so I was't willing unless there was no choice (and I also happen to like Notepad++ quite a bit, that might have blinded me).

However, I was googling the wrong question; as soon as I rephrased my search, I found everything I needed. It is extremely simple to achieve this using excel VBA.

Source:

• Apply a macro to all files in prompted folder

I basically recorded a macro to transpose the 2 columns into rows and deleted the original columns, so that only the transposed rows would show. Then, I copied that macro and pasted it inside another one (see source) which loops through all files in a folder (and added another piece of code to automatically save and close the files).

With this new macro, there is no need to open any file; simply open excel, insert the macro and run it. Voilà.

Final macro:

   Sub LoopThroughFiles()
        Dim xFd As FileDialog
        Dim xFdItem As Variant
        Dim xFileName As String
        Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
        If xFd.Show = -1 Then
            xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
            xFileName = Dir(xFdItem & "*.txt*")
            Do While xFileName <> ""
                With Workbooks.Open(xFdItem & xFileName)
                'Start of transpose code
                Range("A1").CurrentRegion.Select
                Selection.Copy
                Range("C1").Select
                Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=True
                Columns("A:B").Delete
                Range("A1").Select
                Application.CutCopyMode = False
                'End of transpose code
                'Save and close
                ActiveWorkbook.Close SaveChanges:=True
                'End of save and close
                End With
                xFileName = Dir
            Loop
        End If
    End Sub

It was so simple I feel stupid now, sorry to all who had to read the post and rolled their eyes.