Formula to change ID format to a new UniqueID column (1 to 0001)

(0) Share

Report

Posted on by nraindmia

Hi all,

I have this SharePoint list, now I want to create the prefix '0000' for the UniqueID from ID.

Example:

If the ID is 1, I want the Unique ID to become 0001

If the ID is 10, UniqueID becomes 0010,

If the ID is 100, UniqueID becomes 0100,

If the ID is 1000, UniqueID becomes 1000,

I did some research about this, I tried these 2 formulas that I already put inside the SharePoint list column formula for calculated

=TEXT(ID, "0000")

=CONCATENATE([Field value],REPT("0",MAX(0,4-LEN(ID))),ID)

But somehow the formula keeps on changing when I refresh or create a new item. Sometimes it will appear 0009 instead of 9, but sometimes it will appear 0000, sometimes it will appear 0, and sometimes It will appear 9 every time I refresh

I also use this calculation

=RIGHT(CONCATENATE("0000",ID),4)

But as you can see, it will appear 0000 (bottom)

Do you have any other way on how to solve it?

thanks!

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @nraindmia ,

Considering the limitations of SharePoint, the ID field is automatically generated and cannot be directly used in a calculated column.

I suggest you create a text-type UniqueID column, then use power automate to update it. I've made a test for your reference:

1\My SP list

2\The flow

3\Result

Best Regards,

Bof

Was this reply helpful? Yes No
nraindmia on at

Like (0)

Report
Copy link

Link copied!

Hi @v-bofeng-msft ,

Appear this error. The uniqueID is a text column right?

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @nraindmia ,

The uniqueID is a text column right?

Yes. Could you please show me your flow?

Best Regards,

Bof

Was this reply helpful? Yes No
Verified answer

SanmeshG 1,947 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi @nraindmia ,

You can get this by just applying this JSON formatting to the calculated column and you should be good. However this doesn't return the value displayed in the view when you read list items from flow.

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json", "elmType": "div", "txtContent": "=if([$ID] > 999, [$ID], if([$ID] >= 100, '0' + [$ID], if([$ID] >= 10, '00' + [$ID], if([$ID] <= 9, '000' + [$ID], ''))))" }

If this helps , do give this a thumbs up and mark this as a solution.

Thanks,

Sanmesh

Was this reply helpful? Yes No
nraindmia on at

Like (0)

Report
Copy link

Link copied!

Hi @SanmeshG ,
Your code is working. Thanks a lot !

Was this reply helpful? Yes No