Replace letters with blank values

[Matthew_Pupa]

Going to make another request on here for anyone who has done this…

I’m trying to look through characters in the records in one of my columns (it’s a combination of letters and numbers), and I’m trying to get rid of the letters so only numbers remain. For example, “I have 3 dogs” would get changed to “3”.

Tried doing something like the below, but no luck…

SUBSTITUTE(twilio_text, OR(“a”,“e”,“i”,“o”,“u”),"")

I tried the same thing with AND() and no luck either.

I know if I can do this, I’ll probably need to combine the result with TRIM() to get only the number value.

Has anyone done anything like this before?

To be transparent, I got this recommendation for a different column I was trying to create…

IF(
** FIND(‘black’, Text) > 0, **
** ‘black’,**
** IF(**
** FIND(‘white’, Text) > 0, **
** ‘white’,**
** IF(**
** FIND(‘brown’, Text) > 0, **
** ‘brown’**
** )**
** )**
)

I get using the IF(FIND()) combination technically could work, but it would be pretty insane doing that for a large range of numbers (1 - 1000)

Matt

[Matthew_Pupa]

I found a solution based off a solution to a previous post. I’m sharing it below in case it helps someone else at some point. This accounts for some (but not all) special characters. Someone could take this if they need it, and add additional characters to exclude.

LOWER(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(LOWER(“text”),“a”,""),
“b”,""),
“c”,""),
“d”,""),
“e”,""),
“f”,""),
“g”,""),
“h”,""),
“i”,""),
“j”,""),
“k”,""),
“l”,""),
“m”,""),
“n”,""),
“o”,""),
“p”,""),
“q”,""),
“r”,""),
“s”,""),
“t”,""),
“u”,""),
“v”,""),
“w”,""),
“x”,""),
“y”,""),
“z”,""),
“!”,""),
“@”,""),
“#”,""),
“$”,""),
“%”,""),
“^”,""),
“&”,""),
“*”,""),
“(”,""),
“)”,""),
“?”,""),
“,”,""),
“!”,""),
“.”,""),
“~”,""),
“’”,""),
“é”,"")
)