How can I reorder names in column A based on rearranged house numbers in column B using Numbers?

Hi Stephen,

Thanks for the screen shot. Your table does look rather "Excel-like". The Excel approach is to have a huge "ocean" of cells on each sheet, with small "islands" of data dotted here and there. I think your error arises from columns with unrelated data and blank rows.

Might I suggest that you follow the Numbers approach where each sheet is a blank "canvas" where we can place objects such as tables (each with a purpose).

Try this. Row 1 is a Header Row.

Data in Columns A and B are typed or pasted in.

Following on from SG's suggestion of TEXTBEFORE, formula in C2 is TEXTBEFORE(B2," ")×1

Fill down.

The x1 (entered as *1) forces the result to become a Number, rather than Text. Then they will sort correctly. Sort by Column C.

Formula in D2 is TEXTAFTER(B2," ")

Fill down.

That allows you to have other streets in the same table.

For example,

Then you can sort or filter by Street Name or create a Pivot Table to summarise the data.

The usual advice in this forum is to keep your data in a single table and create summaries, rather than to split your data over several tables (or sheets) and then try to combine values.

Please call back with questions.

Regards,

Ian.

One way is to do something like this:

Add a column C with this formula in C2, filled down the column:

=REGEX.EXTRACT(B2,"\d+")*1

Click the C column letter and choose 'Sort Ascending' from the dropdown.

Use ; in the formula instead of , if your region uses , as the decimal separator, eg:

=REGEX.EXTRACT(B2;"\d+")*1

The "\d+" means match things that look like number digits.

The multiplication by 1 coerces the extracted string to a number for sorting.

REGEX.EXTRACT - Apple Support

If you don't like REGEX and the house numbers are always before the first space then you could also use:

=TEXTBEFORE(B2," ")*1

SG