# Converting text to a number

**URL:** <https://community.fibery.io/t/converting-text-to-a-number/8471>\
**Category:** API & Programming\
**Created:** [March 19, 2025, 12:31pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471 "2025-03-19T12:31:15Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![tomvb](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/tomvb/32/8764_2.png) [@tomvb](https://community.fibery.io/u/tomvb)\
**Post date:** [March 19, 2025, 12:31pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/1 "2025-03-19T12:31:15Z")

</div>

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](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375). However, none of the tricks in there worked for me

---

<div class="post-metadata">

**Author:** ![tomvb](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/tomvb/32/8764_2.png) [@tomvb](https://community.fibery.io/u/tomvb)\
**Post date:** [March 19, 2025, 12:39pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/2 "2025-03-19T12:39:06Z")

</div>

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](https://community.fibery.io/t/location-field-street-address/4477/1))

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

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [March 19, 2025, 3:15pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/3 "2025-03-19T15:15:11Z")

</div>

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

```auto
Vespuccistraat 1
Vespuccistraat 19
Vespuccistraat 20
```

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [March 19, 2025, 3:20pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/4 "2025-03-19T15:20:25Z")

</div>

Or you could even use @Matt_Blais’s [clever formula for converting numbers to text](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/14).  
And you might be able to simplify if you can assume the numbers will never be more than 999 for example.

---

<div class="post-metadata">

**Author:** ![tomvb](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/tomvb/32/8764_2.png) [@tomvb](https://community.fibery.io/u/tomvb)\
**Post date:** [March 19, 2025, 3:32pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/5 "2025-03-19T15:32:57Z")

</div>

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

Formula:

```auto
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](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/c/c85ae52afea63d6eecb51657f648cd47dddeb11c.png)

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [March 19, 2025, 3:54pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/6 "2025-03-19T15:54:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tomvb](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/tomvb/32/8764_2.png) [@tomvb](https://community.fibery.io/u/tomvb)\
**Post date:** [March 19, 2025, 6:16pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/7 "2025-03-19T18:16:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [March 19, 2025, 6:54pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/8 "2025-03-19T18:54:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [March 19, 2025, 8:53pm UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/9 "2025-03-19T20:53:25Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![tomvb](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/tomvb/32/8764_2.png) [@tomvb](https://community.fibery.io/u/tomvb)\
**Post date:** [March 20, 2025, 8:52am UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/10 "2025-03-20T08:52:21Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/f/f0fd0b1b75d4b009e8a38ec84d8299876d97b03c.png)

---

<div class="post-metadata">

**Author:** ![tomvb](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/tomvb/32/8764_2.png) [@tomvb](https://community.fibery.io/u/tomvb)\
**Post date:** [March 20, 2025, 8:53am UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/11 "2025-03-20T08:53:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [March 20, 2025, 9:09am UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/12 "2025-03-20T09:09:58Z")

</div>

> [@Matt\_Blais](#):
>
> @tomvb You should be able to simplify that formula to this:

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

> [@tomvb](#):
>
> I created a new db and now it works, strange!

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.

---

<div class="post-metadata">

**Author:** ![tomvb](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/tomvb/32/8764_2.png) [@tomvb](https://community.fibery.io/u/tomvb)\
**Post date:** [March 20, 2025, 9:38am UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/13 "2025-03-20T09:38:22Z")

</div>

Aah, that makes sense, thanks Chris!

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [April 29, 2025, 11:29am UTC](https://community.fibery.io/t/converting-text-to-a-number/8471/14 "2025-04-29T11:29:21Z")

</div>