Converting text to a number

API & Programming
1

I have a formula that uses ReplaceRegex() to remove everyting but the last number from a string. This leaves me with the number I need. However, this number is saved as Text. I need it to be a number, so I can use it to sort my data in a proper way.

There seems to be no toNumber or toInt type function. Is there a way of doing this?

I found this discussion: Convert text to number or date with formulas. However, none of the tricks in there worked for me

2

To add more context: I want to sort a list of addresses by [streetname] [house number]. I’m using the Location type field.

I’m extracting the [streetname] [house number] by using this formula: Left(FullAddress(Adres), Find(FullAddress(Adres), ",") - 1) as the house number is not properly added to the addressparts (see Location field street address)

This leaves me with a list of addresses that I can sort on. However, as they are Text, the sorting creates a list like this:

Vespuccistraat 19
Vespuccistraat 1
Vespuccistraat 20

My thinking is that if I can extract the house number as a Number, I can use it to properly sort this list

3

If we can’t get real numeric sorting, one workaround would be to left-space-pad the number-text, i.e.:

Vespuccistraat   1
Vespuccistraat  19
Vespuccistraat  20
1 Like
4

Or you could even use @Matt_Blais’s clever formula for converting numbers to text.
And you might be able to simplify if you can assume the numbers will never be more than 999 for example.

5

I tried that, but perhaps I’m doing something wrong?

Formula:

If( MatchRegex(HousenumberText,"[^0-9]"), -9999,
  If( Length(HousenumberText)<1, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-0, 1)) - 1) + 10 *
  If( Length(HousenumberText)<2, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-1, 1)) - 1) + 10 *
  If( Length(HousenumberText)<3, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-2, 1)) - 1) + 10 *
  If( Length(HousenumberText)<4, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-3, 1)) - 1) + 10 *
  If( Length(HousenumberText)<5, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-4, 1)) - 1) + 10 *
  If( Length(HousenumberText)<6, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-5, 1)) - 1) + 10 *
  If( Length(HousenumberText)<7, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-6, 1)) - 1) + 10 *
  If( Length(HousenumberText)<8, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-7, 1)) - 1) + 10 *
  If( Length(HousenumberText)<9, 0, (Find("0123456789", Middle(HousenumberText, Length(HousenumberText)-8, 1)) - 1)
))))))))))

Result:
Screenshot 2025-03-19 at 16.31.20

6

Is it possible that the formula to extract the house number has a leading or trailing space (which will be trimmed on the UI)?

7

I tried the formula on a regular textfield, but I get the same result

8

I don’t know what to say. I just copy pasted the formula you wrote into a db and it worked fine for me :person_shrugging:

1 Like
9

@tomvb You should be able to simplify that formula to this:

If( MatchRegex(HousenumberText,"[^0-9.]"), -9999,
  If(Length(HousenumberText) > 8, HousenumberText,
    Left("00000000", 9-Length(HousenumberText))
)
1 Like
10

I created a new db and now it works, strange!

image
image398×188 8.61 KB

11

Interesting, I’ll give it a try. Thanks!

12

I think this will still result in a text string, which is not what you need, AFAIU

Just FYI, sometimes a formula can be invalid for one entity in the db, and this will basically stop it from correctly updating across the whole db (leaving the most recently calculated values intact). This can give the impression that the formula is not working for the entity you are looking at, when in fact it is some other entity in the db which is breaking things.

3 Likes
13

Aah, that makes sense, thanks Chris!