2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Word tables to Excel

So this is a doozy. I have about 60 word files on a SharePoint site. Each of the word files is a table, similarly formatted, with similar columns. My goal is to get the data within the columns into an excel file.

The tables are formatted in such a way that when you paste into excel, sometimes it is pasted into multiple rows when it’s not immediately clear that would be the case in the word file.

Ultimately the last column is something like languages and lists all the different languages related to that row.
What I want to happen is split that out into excel so each language is on its own row.

Is there any better way than manually going through all the files, pasting, taking the overflow into the next rows down into the cell above (essentially unmerging the cells), and pulling out each language into its own row?

I’ve tried a =TRIM( and a =TRANSPOSE(TEXTJOIN( but it is still cumbersome and doesn’t work all the time. Plus, ideally could assign a chunk of files out to other people and they would not have the same proficiency with formulas as me.

Any help or advice is appreciated! Thanks!!

submitted by /u/oneworrytoomany
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#rows.com
#Word tables
#Excel
#SharePoint
#Data extraction
#Column
#Table
#Languages
#Row
#Pasting
#Formulas
#TRIM
#TRANSPOSE
#TEXTJOIN
#Unmerging cells
#Data transformation
#Automation
#Manual process
#File conversion
#Data splitting