Need help w converting xls formula used to extract numbers from a field to Alteryx formula

I am currently using the following formula in excel to extract numbers from a field with (text + numbers):

IF(SUM(LEN([@[Layer Name2]])-LEN(SUBSTITUTE([@[Layer Name2]], {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&[@[Layer Name2]], LARGE(INDEX(ISNUMBER(--MID([@[Layer Name2]],ROW(INDIRECT("$1:$"&LEN([@[Layer Name2]]))),1))* ROW(INDIRECT("$1:$"&LEN([@[Layer Name2]]))),0), ROW(INDIRECT("$1:$"&LEN([@[Layer Name2]]))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN([@[Layer Name2]])))/10),"")

I am new to Alteryx and cannot find corresponding functions in Alteryx to make the above into an Alteryx expression.

Can you pls help with translating the xls formula into an Alteryx expression?

Expression

Accepted answers

AnnaMikhaylova

Hi @dtrix, based on your sample data, you need to extract final layer number only if you have a word "layer" in your data set or word "MP".

In that scenario, I would suggest to first separate data set in two using Contains formula in filter tool:

Contains([Layer Name2],"Layer") OR Contains([Layer Name2],"MP")

Then use RegEx a tool which allows to identify patterns to find first single digit contained in the data set. You will need to configure in the tool:

Column to Parse: Column name that contains data
RegEx expression: /d{1} - which will be looking for a single numeric character within the string.
Output method: Tokenize
Split to 1 Column

Solution for forum 15 Sep.PNG

Then you can union data set back together, so you have full results.

It should give you the result that you are after.

Solution for Forum 15-Sep-2023.yxmd

All comments

caltang

Instead of that - can you provide some sample data and the expected output in Excel? It'll be easier to convert with Alteryx processes once we can see some data.

Rags1982

Agree with @caltang

Sometimes with Alteryx it's easier to start a fresh and just build your formulas from the beginning again rather than try to convert Excel formulas. There's probably a couple Alteryx tools, or even some good old Regex that can do what you need. But will need to see some data first.

dtrix

Thanks.

From the attached you can see "Layer Name2" in column A. In column B, I input the excel formula I mentioned, which is working by checking for any " -", ":" and " :" and extracting the characters on the left side of those. If it can't find any of those, then using iferror, I try to get the numbers out of the field. Then I kind of do it again column C to extract numbers from the rest of the returned field in column B.

Hope this provides context. Also, not sure what Regex does.

layer mapping testing.xlsx

AnnaMikhaylova

Hi @dtrix, based on your sample data, you need to extract final layer number only if you have a word "layer" in your data set or word "MP".

In that scenario, I would suggest to first separate data set in two using Contains formula in filter tool:

Contains([Layer Name2],"Layer") OR Contains([Layer Name2],"MP")

Then use RegEx a tool which allows to identify patterns to find first single digit contained in the data set. You will need to configure in the tool:

Column to Parse: Column name that contains data
RegEx expression: /d{1} - which will be looking for a single numeric character within the string.
Output method: Tokenize
Split to 1 Column

Solution for forum 15 Sep.PNG

Then you can union data set back together, so you have full results.

It should give you the result that you are after.

Solution for Forum 15-Sep-2023.yxmd

dtrix

Hi @AnnaMikhaylova

Thanks for the solution. This helped tremendously. Just an added question - how do you get the formula to return a number only when there's a standalone single digit in the field? The workflow solution you attached would return 2 if the field were 250xs250 and I dont want it to return anything in such scenario. I only want it to return 2 if the number 2 itself is present in the field such as in "2 250xs250" or "2: 250xs250" or "2 - 350xs650", "350 xs 2 xs 650" etc.

Thanks in advance!

AnnaMikhaylova

Hi @dtrix,

For additional scenarios, in cases where you have a single digit followed by a space ("2 250xs250" or "2 - 350xs650", "350 xs 2 xs 650") I would suggest adjusting Regular Expression in RegEx tool to: (\d{1}\s).

For "2:" cases, I would consider the data set you have, how many numbers you want to extract are followed by special characters and which special characters. Then consider replacing cases of "2:" with "2 :".

If you don't have many variations of special characters in your data set, then you can use a Formula tool: Replace([Layer Name],":"," :") OR Replace([Layer Name],"-"," -") and so on. By adding a space it will resolve issue of identifying issue of secondary pattern.

dtrix

Thanks @AnnaMikhaylova, I will try that.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703