Skip to main content

Notifications

Community site session details
Power Automate - General Discussion
Answered

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

Like (0) ShareShare
ReportReport
Posted on 25 Mar 2024 02:47:19 by

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)

nraindmia_0-1711334744982.png

 

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

 

thanks!

  • nraindmia Profile Picture
    on 25 Mar 2024 at 08:57:17
    Re: Formula to change ID format to a new UniqueID column (1 to 0001)

    Hi @SanmeshG ,

    Your code is working. Thanks a lot !

     

    nraindmia_0-1711357029956.png

     

  • Verified answer
    SanmeshG Profile Picture
    1,947 Super User 2025 Season 1 on 25 Mar 2024 at 05:09:50
    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], ''))))"
    }

    SanmeshG_1-1711343358746.png

     

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

     

    Thanks,

    Sanmesh

  • v-bofeng-msft Profile Picture
    on 25 Mar 2024 at 03:50:12
    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 Profile Picture
    on 25 Mar 2024 at 03:22:27
    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?

    nraindmia_0-1711336812540.png

     

  • v-bofeng-msft Profile Picture
    on 25 Mar 2024 at 03:11:32
    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

    vbofengmsft_0-1711336044494.png

    2\The flow

    vbofengmsft_2-1711336285156.png

     

    3\Result

    vbofengmsft_1-1711336215530.png

     

    Best Regards,

    Bof

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Leaderboard > Power Automate - General Discussion

#1
stampcoin Profile Picture

stampcoin 23

#2
Pstork1 Profile Picture

Pstork1 20 Most Valuable Professional

#3
Gprior Profile Picture

Gprior 19

Overall leaderboard
Loading started
Loading started