# Convert text to number or date with formulas

**URL:** <https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375>\
**Category:** Ideas & Features\
**Tags:** formulas\
**Created:** [January 12, 2022, 11:02pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375 "2022-01-12T23:02:09Z")\
**Posts on this page:** 1\
**Showing post:** 14

<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:** [November 9, 2023, 3:14pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/14 "2023-11-09T15:14:35Z")

</div>

Here’s a workaround - The following Formula will convert the `Name` text field to an **Integer only** (NOT floating point) up to 9 digits (but you could add more).

If first “If” sets the value returned (-9999) if any non-digit characters are found in the `Name` field.

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

```

This could be further tweaked to ignore specific non-numeric characters like “.” and “-”, by adding `RegexReplace()` to _each line_ – e.g:  
`... (Find("0123456789", RegexReplace(Name, "[.,/-]", ""), Length( ...`

That would only be useful if each numeric sub-field is fixed-length (though additional bewildering complexity could possibly solve some of that 🤯 ).

---

_[View the full topic](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375)._