Forum Discussion

DirkRuana's avatar
DirkRuana
Qrew Captain
6 years ago

Formula Text Field Formatting

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

Tables and fields
  • JordanMcAlister's avatar
    JordanMcAlister
    Qrew Captain
    6 years ago
    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"
  • DirkRuana's avatar
    DirkRuana
    Qrew Captain
    6 years ago
    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"

  • JordanMcAlister's avatar
    JordanMcAlister
    Qrew Captain
    6 years ago
    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.
  • JordanMcAlister's avatar
    JordanMcAlister
    Qrew Captain
    6 years ago
    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")