Convert text to number or date with formulas

Ideas & Features
14

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.

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 :exploding_head: ).