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

Like (0) Share

Report

Posted on 25 Mar 2024 02:47:19 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!

All responses (5)

Answers (1)

nraindmia on 25 Mar 2024 at 08:57:17

Like (0)

Report

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

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

SanmeshG 1,938 Super User 2025 Season 1 on 25 Mar 2024 at 05:09:50

Like (1)

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

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
v-bofeng-msft on 25 Mar 2024 at 03:50:12

Like (0)

Report

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

Hi @nraindmia ,

The uniqueID is a text column right?

Yes. Could you please show me your flow?

Best Regards,

Bof
nraindmia on 25 Mar 2024 at 03:22:27

Like (0)

Report

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

Hi @v-bofeng-msft ,

Appear this error. The uniqueID is a text column right?
v-bofeng-msft on 25 Mar 2024 at 03:11:32

Like (0)

Report

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

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