Hello Alteryx community,
I am trying to extract/update a column value when it has some leading 000's before the actual value.
Weight 1 | Weight 2 |
4 | 0000000009,700 |
4 | 0000000006,700 |
51749 | |
4 | 0000000012,500 |
286 | |
724 | 000007134,500 |
I would like to extract the values from Weight 2 (V_String) when it starts with a number other than 0 from the left-hand side.
For example, 0000000009,700 should be equal to 9700
If possible, I would also like an answer on how to convert 0000000009,700 to 9.7 or 0000000009,712 to 9.712
IF IsEmpty([Weight 2]) THEN [Weight 1] ELSE [Weight 2] ENDIF
I am using the above Formula
Solution Table
Weight 1 | Weight 2 |
4 | 9700 |
4 | 6700 |
51749 | |
4 | 12500 |
286 | |
724 | 7134500 |
I am attaching the workflow below as an example.
Thank you,
ssrip4
Solved! Go to Solution.
Hi @ssrip4
Try this formula:
IF IsEmpty([Weight 2]) THEN [Weight 1] ELSE trimleft(Replace([Weight 2],',',''),'0') ENDIF
For converting to have the decimals, you could just divide by 1000.
Hi @ssrip4 ,
Please check the post below. This lists multiple ways of removing the leading zero's.
Remove Leading Zero - Alteryx Community
In order for you to introduce the decimals, you can simply use the 'Replace' function.
Replace([Field1], ',', '.')
Best,
Jagdeesh Narayanan
You can also use the following IF formula for Weight 2:
IF IsEmpty([Weight 2]) THEN ToNumber(regex_replace(ToString([Weight 1]),',','.')) ELSE ToNumber(regex_replace([Weight 2],',','.')) ENDIF
Hi @ssrip4
If your data is consistent and doesn't use a comma separator for thousands, you can use the below in one formula tool to get the desired output.
IF IsEmpty([Weight 2]) THEN [Weight 1] ELSE ToNumber(Replace([Weight 2], ',', '.')) ENDIF
Cheers!
Phil
Thank you for all the answers. I hope I can accept all of your answers as solution.
Hello again,
Just one more small help. I am trying to "Replace" characters within a String. I was able to achieve that by using the Formula tool. But the thing here is I was unable to generate a single formula to reach the end output.
Sample Data
Data |
+0F1 |
+0F1+5A1 |
+0F1+1A2+2C4 |
+4C4+9C3+8F2+7C2 |
Output
Data |
0F1 |
0F1,5A1 |
0F1,1A2,2C4 |
4C4,9C3,8F2,7C2 |
I was able to achieve the result in the below way. I would like to know if there is a better or faster way of getting the same output instead of running the formula separately.
Formula Screenshot
I can create a new post if required and you can answer it there.
Thank you,
ssrip4
Hi @ssrip4
Try the following formula:
TrimLeft(Replace([Data], '+', ','),',')
The added TrimLeft statement gets rid of the lead in comma after they are swapped in. I wouldn't recommend using a RegExReplace in this situation.
Cheers!
Phil
Thank you @Maskell_Rascal