Forum Discussion
Preparing the text file prior to importing to Excel
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.
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)
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.
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.
