How to sort on alphabetic data in a column with numbers and alphabetic data

Question

In a column with street addresses, I want to sort on the street name and not the house number.

For example, with and address of 100 Grant St. I want to sort the street name "Grant" and not the house number "100".

If I have to split the address into two columns, street number, and street name, what is the easiest way to do this.

Answer 1

Hi Silas,

From what I check, this is essentially a feature limitation of Excel’s standard sort. When you sort a column, Excel always evaluates the entire cell from left to right, so if your data begins with a number like “100 Grant St,” it will sort by the number first, not the street name.

The simplest way to sort by street name is to split the address into two columns: one for the house number and another for the street name. The easiest method is to use Excel’s “Text to Columns” feature:

1. Select the column with addresses.
2. Go to Data → Text to Columns → Delimited.
3. Choose a space as the delimiter.
4. This will separate the number from the street name into two columns (you may need to adjust if street names have multiple words).

Answer 2

Hi @Silas Hart,

If you want a formula solution, you can use the following formula.

Using a formula avoids having to split the data into different columns and then sort the data.

The formula is dynamic.

=SORTBY(B2:B16, BYROW(B2:B16, LAMBDA(a, TRIM(REGEXREPLACE(a, "^\d+\s*", "")))))

I hope this helps. If so, please mark my answer as a Valid Answer and kindly upvote it.

IlirU

Answer 3

Use a helper column that contains only the street name, then sort by that column.

One simple approach:

1. Assume full address is in column A, starting in A2 (e.g., 100 Grant St.).
2. In B2, extract the street name with a formula, for example if the pattern is always "number + space + street name":

      =TRIM(MID(A2,FIND(" ",A2)+1,255))
   

   This removes everything up to the first space (the house number) and returns the rest (e.g., Grant St.).
3. Fill the formula down column B for all rows.
4. Select any cell in the data range (including the helper column).
5. On the Data tab, choose Sort (or Custom Sort), and set Sort by to column B (the street name column), order A to Z.

If the address formats vary, first split the column using Data > Text to Columns and delimit by space, then recombine the parts that make up the street name into a helper column and sort by that column.

Excel sorts entire rows based on the column chosen, so all address data will stay together while being ordered by street name.

---

References:

• Sort data in a range or table in Excel