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