Extract number from a field with leading 0000's and update values fro

[ssrip4]

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

[Luke_C · Accepted 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.

[JagdeeshN · Accepted solution]

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

[chukleswk · Accepted solution]

You can use the following formulas to convert it to a decimal or to a whole number:

 

[chukleswk · Accepted solution]

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

 

[Maskell_Rascal · Accepted solution]

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

[ssrip4]

Thank you for all the answers. I hope I can accept all of your answers as solution.

[ssrip4]

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

[Maskell_Rascal · Accepted solution]

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

[ssrip4]

Thank you @Maskell_Rascal