Getting the street number from a street address

[Erike]

Hello.

I need to get an address for the correct geographical location.

For this I need the street number, the town, the zip code and the state.

The problem I have is with the street address. Customers provide us with an address that includes the apartment and the floor. For example, they tell us calle Alcalá 222 4 izquierda (izquierda is left in Spanish)

That means that his address is in Calle de Alcalá 222. It is a fourth floor, apartment on the left.

And usually this is written in many different ways:

calle Alcalá 222 4 izquierda
Alcalá 222 4Izq
Calle Alcalá 222 4º izquierda
Alcalá 222 4-Izq

For purposes of geographical location what is needed is calle Alcalá 222.
I have managed to solve it but with the help of 3 virtual columns.

The first column determines the number: 222 and it does it in the following way

TOP(EXTRACTNUMBERS([shippingAddressStreet]),1)

This generates a list type field with a single value that is 222.

I have not been able to find an expression that converts this list field into a text field

I do it by creating another virtual column of type text whose content is equal to the previous column.

Once I have that second column of text what I do is

CONCATENATE(LEFT([shippingAddressStreet], FIND([Ex Address 2], [shippingAddressStreet])-1), [Ex Address 2])

Where [Ex Address 2] is the virtual column 2

I already have calle Alcalá 222.

What I am looking for is an expression that allows me to move from a LIST field to a Text field in order to avoid using so many virtual columns.

This solves almost all directions, but there is a special case that I am not able to solve.

Occasionally there are street numbers that are duplicated. For example, there is a building at calle Alcalá 222 and another one at calle Alcalá 222 Duplicated.

From the point of view of geographical location it is not so important. These buildings are next to each other.

The problem comes with the nomenclature of the address. They are usually written like this:

calle Alcalá 222B 4-Izq
calle Alcalá 222-B 4-Izq
calle Alcalá 222 B 4-Izq
calle Alcalá 222 Dup 4-Izq
calle Alcalá 222Dup 4-Izq

The problem comes when there is a letter attached to the number, for example 222B or 222Dup. In those cases the algorithm does not work properly.

When the customer writes calle Alcalá 222B 4-Izq

TOP(EXTRACTNUMBERS([shippingAddressStreet]),1) returns the value 2220000000

Any suggestions on how I can solve this?

[Steve · Accepted solution]

Erike:

TOP(EXTRACTNUMBERS([shippingAddressStreet]),1)

This generates a list type field with a single value that is 222.

I have not been able to find an expression that converts this list field into a text field

Use INDEX(EXTRACTNUMBERS([shippingAddressStreet]),1) instead.

[Erike]

Thanks Steve. That worked perfect!!

Do you have a suggestion for the second issue?

Erike:

When the customer writes calle Alcalá 222B 4-Izq

TOP(EXTRACTNUMBERS([shippingAddressStreet]),1) returns the value 2220000000

Any suggestions on how I can solve this?

[LeventK]

@Erike
I believe AppSheet interprets 222B as 222 Billion as it’s a scientific notation.

[Erike]

Your comment makes a lot of sense.

[LeventK]

Erike:

TOP(EXTRACTNUMBERS([shippingAddressStreet]),1)

Provided the street number is always 3 digit, you can try with this:

NUMBER(LEFT(CONCATENATE(TOP(EXTRACTNUMBERS([shippingAddressStreet]),1)),3)

[Erike]

Thanks @LeventK LeventK

Unfortunately is not always 3 digits. Normally it a number from 1 to 3 digits. No street is long enough for requiring 4 digits yes

[Steve]

Erike:

That worked perfect!!

Hooray!

Erike:

Do you have a suggestion for the second issue?

Unfortunately, no. AppSheet’s text processing capabilities are limited, and really not up to the complexity of this particular challenge. Were I in your place, I might consider trying to do the processing in the spreadsheet using regular expressions (which are themselves a huge complexity).

[Erike]

Will explore that possibility.
I imagine the implications are:
Need to define a new real column in the spreadshhet
Add any expression required in the that spreadsheet column
Define the same real column in appSheet.

Thanks in advance

[Steve]

Erike:

Need to define a new real column in the spreadshhet
Add any expression required in the that spreadsheet column
Define the same real column in appSheet.

Correct!

Unfortunately, the result of the spreadsheet formula will only be available after a sync. Keep that in mind if you try to use the result in other expressions within the app.