If a cell contains Text

I am trying to clean some data before importing it into a file. I CANNOT change the original source.

Working on a Zip Code field, but sometimes it contains letters for other countries. Is there a way to highlight cells that contain text? I am wanting to replace any cell that contains text with a zero. Not sure how to specify in a formula that it is text.

Any help appreciated

Text Mining

Accepted answers

alexnajm

IIF(Regex_Match([Field],".*[[:alpha:]]+.*"),"0", [Field])

If a cell contains Text.yxmd

All comments

alexnajm

IIF(Regex_Match([Field],".*[[:alpha:]]+.*"),"0", [Field])

If a cell contains Text.yxmd

Carolyn

RegEx!

You can use RegExMatch to flag when text is present and then filtered for when a non-number is found (I used \D = not a number - it'll catch spaces and special characters too)

REGEX_Match([Zip], ".*\D.*")

You can use RegExReplace to replace a non-number with a 0

REGEX_Replace([Zip], "\D", "0")

See attached workflow example

RegEx.yxmd

DodgerFH

Thanks! This worked perfectly. Really appreciate the quick response. Have a great day!

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335