Search for keyword and returning characters in front

[rvuich]

Hi, I am converting utility invoices from PDF to Excel format and would like to build a workflow for taking those excel files and finding the energy data and running that through alteryx. For example, from the Excel file I'd like to search for "Therms" or "kWh" and have alteryx return the numerical value and the unit. Is this possible? 

Thanks!

[phottovy]

Hi @rvuich ,

I put together the attached workflow that parses the numbers before either of those two terms using the following regex:

.*\s([\d\.,]+)(Therms|kWh)

Here is a breakdown of the expression:

.* = Zero or more of any character

\s = space (I made the assumption that a space came before the number, this might have to be modified if there is no space)

([\d\.,]+) = parse any numerical digits, periods, or commas before the words "Therms" or "kWh"

(Therms|kWh) = parse either of the values "Therms" or "kWh"

[rvuich]

That's super helpful! Is there a way to have it search the whole sheet instead of limiting it to a column? Since that data won't always be in the same columns. 

Thanks!

[phottovy]

You can always use the transpose tool to get all of the columns you want to search in one column. Attached is a slightly modified example. You might have to use additional transpose and crosstab tools to reorganize your original data.