Hello community:
Have a data set of contact information that was entered such that the information is now various columns "off", depending on row. (some rows shifted over multiple columns to the right, some rows shifted over a single column to the right, etc)
Have experimented using the REGEX_Match with the "Formula" function to comb through each of the columns, to select the desired data based on if the data in the columns matches the criteria desired (for example, phone number format: ###-###-####. My REGEX looks in the columns around the "phone" column and if it finds data that matches the criteria, it selects it.
Example of the this formula:
If
REGEX_Match([Phone],"\d{3}\W\d{3}\W\d{4}$")
THEN [Phone] Elseif
REGEX_Match([Fax],"\d{3}\W\d{3}\W\d{4}$")
THEN [Fax] Elseif
REGEX_Match([Email],"\d{3}\W\d{3}\W\d{4}$")
THEN [Email] Else ""
Endif
This regex is working, but is there a more efficient way to automate this for all columns? That is: be able to specify a criteria, and take the data that matches that criteria from from a selected group of columns? (in reality, my data set contains many more columns than this example, and to go through and add a separate "formula" for each column is going to take ages - there must be a better way)
THANK YOU
