Skip to main content
Question

Formula Text Field Formatting

  • June 18, 2019
  • 0 replies
  • 29 views
Forum|alt.badge.img+8
Hello:

Up that ol creek without a paddle again!

I have two fields in a formula text field

It is created by using [Building - Group Identifier]&"-"&[Record ID#].
Currently it looks like:  04-3757

I need to have a more specific layout: xx-xxx-x-xxx (remaining spaces to the right would be zeros).  So in this example: 04-375-7-000

Help please

Regards,

Dirk

    What would the format look like when the record Id# gets to 5 digits?

    Currently you could use:

    [Building - Group Identifier] & "-" & Left(ToText([Record ID#]),3) & "-" & Right(ToText([Record ID#]),1) & "-000"
    Forum|alt.badge.img+8
    • DirkRuana
    • Author
    • Registered
    • June 18, 2019
    Jordan:

    Let me give that a try.


    Forum|alt.badge.img+8
    • DirkRuana
    • Author
    • Registered
    • June 18, 2019
    That works for now.

    Thank you!

    Wouldn't something like this work? Use of Mid?

    [Building - Group Identifier] & "-" & Left(ToText([Record ID#]),3) & "-" & Mid(ToText([Record ID#]),1, &"-"& Right(ToText(Record ID#]),3"

    The use of Mid will also work! 

    Using Mid, your formula would look something like:

    [Building - Group Identifier] & "-" & Mid(ToText([Record ID#]),1,3) & "-" & Mid(ToText([Record ID#]),4,1) & "-000"

    The Mid function uses two identifiers: the first number after the text you're extracting tells the function where to start, and the second number tell the function how many characters to grab.
    If ever your record id gets high enough to reach 5 digits you could set up an "if" statement where if record id# is less than 9999 it uses one formula and if it's greater, it uses the other.

    If(
    ToText([Record ID#])<"9999",
    [Building - Group Identifier] & "-" & Mid(ToText([Record ID#]),1,3) & "-" & Mid(ToText([Record ID#]),4,1) & "-000",
    [Building - Group Identifier] & "-" & Mid(ToText([Record ID#]),1,3) & "-" & Mid(ToText([Record ID#]),4,1) & "-" & Mid(ToText([Record ID#]),5,1) & "00")
    Terms & ConditionsAccessibility statement