Column sort(Property address: mixture of alphabets and numerics)

[stanley_okafor]

Hello Community,
Please does any one have an idea on how I can sort a column in alphabetical order? Am facing challenge when I try to use the sort column like the screenshot below:

Now as you can see from the screenshot, the issue is that due to the number in the property address the alphabets are still not well sorted. Any idea will help

[Rohan_Mitchell]

You can create a formula column that uses the RegExReplace formula to remove the numbers from the address and then sort on the formula column -

[stanley_okafor]

Thank you @Rohan_Mitchell for the support.
But kindly note that if you remove the number outrightly using RegExReplace then I can no longer identify the property address. I want a way where I can filter out the names so that for example “New Nkisi” will go before any Omu road without removing the proerty identify which is used for payment.

[Agile_Dynamics]

you continue to use the original column for display.
the column with the regex formula is only used as the sort column
and it should be hidden.

max

[Rohan_Mitchell]

Hi @stanley_okafor,

As Max said, in the current scenario you would keep using the original Address column as normal, including keeping it visible, and use the new formula column just for sorting the table (but the formula column can still be invisible).

However, having taken a longer look at your list of addresses, I see a couple of issues that mean my solution will fail for many of the addresses. For example, street numbers like “5A”, and “36 and 38”, and “5 / 7”. So there are a variety of variations in the way the addresses are entered which makes your task more difficult, including some without any “street number” at all, such as “All Saints Cathedral”.

Therefore I think the best approach is to implement multiple columns such as - “Street Number”, “Address Line 1”, “Address Line 2”, “Town”, “State”, “Postcode” (of course, you would use columns that are appropriate for your location. The ones I have listed would work well for Australia where I am from). You can sort the table on “Address Line 1”, and create a final formula column that combines all the address columns as necessary using the Concatenate() formula.

If there will be other people who will be entering data, then I think you will have ongoing issues keeping the data “clean” - that is, ensuring data is entered in a consistent way that meets your requirements (for example, even though you have a specific “Street Number” column, someone will still put the street number in the Address Line 1 field, for example. It just happens. But that is not all that unusual, and most (if not all) systems need at least occasional data cleansing. Coda has many ways in which it can assist that process.

I hope that helps.

Rohan