Excel spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2173433%22%20slang%3D%22en-US%22%3EExcel%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2173433%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20a%20very%20basic%20excel%20user%20so%20I'll%20apologise%20up%20front%20if%20this%20is%20actually%20an%20easy%20fix.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20a%20list%20of%2015000%20addresses%20that%20I%20would%20like%20to%20put%20into%20a%20spreadhseet.%20The%20data%20is%20all%20contained%20in%20one%20line%20and%20is%20not%20easily%20added%20to%20a%20spreadsheet.%20The%20data%20i%20have%20is%20similar%20to%201%2F23%20Smith%20St%2C%20Hometown%2C%201111.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat%20i%20would%20like%20to%20do%20is%20have%20the%20spreadsheet%20columns%20to%20read%20unit%20number%2C%20street%20number%2C%20street%20name%2C%20city%2C%20post%2Fzip%20code.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EBearing%20in%20mind%20there%20are%2015000%20addresses%20is%20there%20a%20relatively%20easy%20way%20to%20do%20this%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20in%20advance.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2173433%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi, I'm a very basic excel user so I'll apologise up front if this is actually an easy fix.

 

I have a list of 15000 addresses that I would like to put into a spreadhseet. The data is all contained in one line and is not easily added to a spreadsheet. The data i have is similar to 1/23 Smith St, Hometown, 1111.

What i would like to do is have the spreadsheet columns to read unit number, street number, street name, city, post/zip code.

Bearing in mind there are 15000 addresses is there a relatively easy way to do this?

 

Thanks in advance.

1 Reply

@Buddy1110 I assume that you end upp with one column and 15000 rows, where each cell contains the entire address string. Are these strings consistent, meaning do they all look alike? For example:

"<unit> / <street number> space <street name> comma <city> comma <zip>". If so, you can use Text To Columns on the Data ribbon to split the address into columns. Separating the street number from the name may be a bit tricky, though, because the name itself may also contain spaces.

 

You say you are a very basic Excel user. But that doesn't mean you can't learn a few new tricks. Just search the net for help "Text to columns Excel" and you'll find plenty of resources that show you how it works in detail.

In your particular case, start by splitting by the slash first. That will create two columns:

<unit> <all the rest>

 

Now split the 2nd column by comma. That will create three columns:

<street number name> <city> <zip>

 

Now move the column with the street address towards the right and split it by space. This will create a number of columns depending on the number of spaces in the street name, but the first column should always contain the <street number>

Now merge the parts of the street name that were separated by a space into one string again. Depending on your Excel version, this can be done with TEXTJOIN, CONCATENATE, CONCAT or simply joining cells together with the &-sign and " ". For example =F1&" "&G1&" "&H1 etc. and copy this all the way down. Copy the end result and Paste as values into another column and move it where you want. Whatever seems easiest to you.

 

Start by saving your original data and work with a copy of it. Save your work after every successful step. If something goes wrong. Start over from the previous step. If you run into problems, come back here.