Preparing the text file prior to importing to Excel

[Jerry_Snell]

I'm trying to get data from a text file into Excel.  How would I go about getting the data from the text file that is listed like this:

Company

address

Phone

Company

address

phone

and so on......  There could be 20-50 or more companies listed vertically like that on a text page.

I'd like to get that data into an Excel Spreadsheet with headers for Column A, B, and C to be

for "Company", "address", and "phone".

Without having to rearrange manually and all the copy and pasting, is there a short cut of some sort by perhaps adding characters at the end of each line, setting up a formula, etc so that the import will place each line of data in its appropriate place in the Excel Spreadsheet?  I've attached a screen shot of the Excel spreadsheet and the text file showing the content and how it appears.

[#2]

@Jerry_Snell Your data seems to be consistently structured. 3 fields, blank, 3 fields, blank etc.

That makes it relatively easy. I have attached a small example with the raw data in Sheet1 and the transposed data in Sheet2, using two different methods. The first will work if you are on the most recent version of Excel as it uses the TRANSPOSE function. The second uses OFFSET and should always work.

[#3]

@Riny_van_Eekelen 

That was very helpful!  Thank you.  I used the OFFSET method and it worked great.  One follow up question:  In sheet 2 of the attached, how would I then separate the city, state and zip into 3 new columns to the right and have the phone being the last column?    (as shown with headers in sheet 3)

[#4]

@Jerry_Snell Start by inserting three three columns in Sheet 2, Copy all and Paste as Values in Sheet3 (step 1). Next, you use Text-to-Columns (on the Data ribbon) to split the address in three columns, using the comma as the delimiter (step 2). Without changing any of the settings, this creates separate columns for  the Address, City and " State ZIP". Next, perform a similar action on the State column, but now using space as the delimiter. Notice that this wants to create three columns again. One blank column (because of the space before the State code), one for the State and one for the ZIP. Before pressing Finish, select the first column in the preview window and have Excel "Skip" the first column! The two other columns can remain General (step 3).

Now, this is all very easy and doesn't take more than two minutes to set-up and execute. If this is a one-time task, it will work perfectly. However, if you would have to do this frequently, on large and "dirty" data sets and with more complex rules for cleaning the data and splitting columns, you might want to invest some time learning how to use "Get&Transform" a.k.a. "Power Query", provided you have access to a PC version of Excel.

I attach a file containing the outcome of every step described above in Sheet3.

[#5]

@Riny_van_Eekelen 

I was able to get step 1 done but when I'm performing the "text to columns" for step 2, it's showing up as the formulas in the columns instead of the actual data.  Any idea on what I'm missing?

See the screenshot.

[#6]

@Jerry_Snell I suspect you did a copy / paste in stead of copy / paste values. Hence, Text-to-columns split the formulae where it found a comma.

[#7]

@Riny_van_Eekelen 

That was it.  Thank you so much!!