Modify a column to prepend text based of number of characters

[Anonymous]

I have a column that contains a series of numbers that run from 1 digit to 9 digit numbers.  Based off the number of digits, I need to modify the number and then prepend some text. 

Example:

1 needs to transform to 0001 and then needs to have ABC prepended.  So, the result is ABC0001

22 needs to transform to 0022 and then needs to have ABC prepended.  So, the result is ABC0022

333 needs to transform to 0333 and then needs to have ABC prepended.  So, the result is ABC0333

4444 needs to transform to 4444 and then needs to have ABC prepended.  So, the result is ABC4444

etc all the way to 999999999

Is there an easy way to determine the number of digits and then transform and prepend?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Go to edit queries,

1. Add column->Format->Add Preifx, enter 00000000 

2. click on "Prefix" column, Add column->Extact->last characters, enter count = 4

3. click on"Last Characters" column, Add column->Extact->first characters, enter count = 1

4.Add column->add conditioanl column

5. click on "number" column, Add column->Extact->length

6.click on "condition1" and  "Last Characters" columns, Add column->merge columns

7.add conditional column

Finally, you could remove other columns, only keep "number" and "condition2" column.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Go to edit queries,

1. Add column->Format->Add Preifx, enter 00000000 

2. click on "Prefix" column, Add column->Extact->last characters, enter count = 4

3. click on"Last Characters" column, Add column->Extact->first characters, enter count = 1

4.Add column->add conditioanl column

5. click on "number" column, Add column->Extact->length

6.click on "condition1" and  "Last Characters" columns, Add column->merge columns

7.add conditional column

Finally, you could remove other columns, only keep "number" and "condition2" column.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[suhasakole]

Hi @Anonymous 

Try to create a calculated column using the following Expression. You might get an error, but check the datatype of the column.

CalculatedColumn = "ABC" & REPT("0", 10-LEN(Table[Columnseriesofnumbers])) & Table[Columnseriesofnumbers]

Hope this will resolve your issue.

Thanks,

[Anonymous]

@suhasakole Thank you!  The issue is more complex.  I need to determine length, then add specific number of preceeding "0", or in the case of a 9 digit number, add nothing.  The other bit of fun, if the modified string of numbers is less than 9 characters to begin with, I need to prepend ABC.  If however the string  if nubmbers was 9 to begin with, I need to add DEF.  

Fun.

So I'm really doing 2 transformations.  First, add in the preceeding "0" then based off that output final output, prepend the ABC or DEF.  

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie