Need help converting a list into Excel spreadsheet

In My Humble Opinion
1

At work, I have a long list that I created in a Word document. Unfortunately I need to convert it into a 3- or 4-column spreadsheet, and I didn’t bother to use columns in my Word document. Below is an example of what my document looks like (obviously it is not copied from the real document). Most lines have three items (number + word + number), but occasionally there will be extra words or comments that will need to be jammed into the spreadsheet as well.

Example of my list:

2348 dog 6
3482 fire 19
4358 Mississippi 9
2476 joke, house 10
2439 shirt 20
9044 blue 15 (minor)
3248 bum 12
9284 basket 9 (low)
8734 book 17

2

Put tabs between your columns and you can just copy-and-paste that block into Excel.

3

If the majority of your data fall into pretty clean columns, then I would just use the text-to-columns function built into Excel and then clean up the outliers manually:

Looks like you’d want to select “space” as your delimiter (the character that triggers the separation between intended columns)

ETA: it will likely start with copying your Word data and simply pasting it into an Excel sheet.

4

Thanks. Any way to do that automatically, or does it need to be done line-by-line?

5

This looks like a good answer. I will try it now.

6

David’s solution will probably be easier for you unless you’re comfortable using advanced search/replace options in Word.

For instance, searching for a digit and a space and replacing it with a digit and a tab (using digit wildcards) would cover the first column and potentially the fourth column.

Space-digit to tab-digit might well cover the third, based on your exemplar posted above. This would only leave you with the rare few edge cases to manually track down.

7

I concur with what @DavidNRockies and @Acierocolotl suggest. With the following refinements.

Do a search/replace for the spaces with “;”. That will be your delimiter because you have some data with commas that I assume belong in one field so you don’t want to use a comma or space as delimiter.

Then, visually review and put back spaces where they are actually needed, ex: 2476;joke,;house;10, where the semi-colon before “house” needs to be a space (I assume).

To do this, use another search and replace but this time you are going to search for “,;” and replace it with a ", " (comma and space). Does that make sense?

Save the scrubbed file as a text file

Import the “;” delimited text file into excel.

8

It gets worse. I’m supposed to enter the data into a template that they sent me, and the template is complete garbage. I’m tempted say screw it and tell them to enter the data into the fucking template themselves if that’s how they want their list.

9

Is the template itself made in Excel ? Or MS Access ?

It may well be that if the above recommendations work for you, then you’ll be able to give them what they actually want without bothering with their template at all.

If the text-to-columns wizard works for you, then – depending on how much clean up you have to do afterward – this shouldn’t take long.

It may be worth considering just doing that – the text-to-columns process – and just sending it back to whoever to see if it meets with their approval.

But I’m not particularly clear on what the end goal is here – who wants what, and why.

10

Maybe you could, I don’t know, just bang it into an Excel sheet and then make it look like their template?