There are several ways to do this in Excel, but one way is to create two columns from the Street column. Ideally, you want to sort the list so the Drake Ave.

As you can see in the example below, the Drake Ave records are not together. If you open this type of list in Excel and sort it on the Street column, you get a numerically sorted list.

And sometimes you can luck out and parse first and last names using Excel’s Convert Text to Columns Wizard.īut what if you need to do door-to-door canvassing to check on neighbors or to inform people about an upcoming ballot measure? This format works fine if you’re creating a mailing label as the post office relies on zip code sorting. Many membership databases or mailing lists are set up with defined fields for First Name, Last Name, Street, City, State, and Zip. For example, if the cell contained 1001 Drake Ave., any of these items could be a substring: When we speak of a substring, we mean a part or subset of the Excel cell’s content. Excel does something similar using Text functions. Some programming languages have dedicated substring functions.

Clean Up the Spreadsheet and Change Cell Formatīefore we have Excel extract text from string, we need to define some things.

How to Extract Street Names using RIGHT Function.

How to Extract Street Numbers Using LEFT Function.

Excel text to column formula