Formatting and Replacing spaces in Excel-CodePudding

I have text in a .txt file that is like so:

Apple, Banana   Carrot, Dragonfruit  Eggplant, Fig, Grape

Apple2, Banana2   Carrot2, Dragonfruit2  Eggplant2, Fig2, Grape2

Apple3, Banana3   Carrot3, Dragonfruit3  Eggplant3, Fig3, Grape3

I wish to paste this into Excel and delimit it via commas. To do this, I use the replace all functionaltiy in the text editor to replace the three spaces between Banana and Carrot, adn to replace the two spaces between Dragonfruit and Eggplant. I also have to get rid of the line spaces. Thus my end result would be:

Apple, Banana, Carrot, Dragonfruit, Eggplant, Fig, Grape
Apple2, Banana2, Carrot2, Dragonfruit2, Eggplant2, Fig2, Grape2
Apple3, Banana3, Carrot3, Dragonfruit3, Eggplant3, Fig3, Grape3

However this takes a lot of time to do when I have hundreds of text files formatted like this. Would there be any way to simplify this process or to reduce the time taken for everything? (Mainly the backspacing, replacing commas isn't as much of an issue)

CodePudding user response：

In Excel, you can convert that string using formulas, VBA or Power Query. If you have Excel 365:

Get rid of the blank lines with the Data => Filter tool
Trim the text which removes extra spaces between the words
Split by the space
Remove the commas (since they are not constant)
Rejoin the array with <comma><space>

    =TEXTJOIN(", ",TRUE,BYCOL(TEXTSPLIT(TRIM(A1)," "),LAMBDA(arr,SUBSTITUTE(arr,",",""))))

You can use the same algorithm in Power Query or VBA. If you need to process hundreds of files, I'd suggest using Power Query. There is information in this forum about reading multiple files from a folder, and processing them, in PQ.