Edit Field to include

[Coral_Hernandez]

Hi there,

I was wondering if someone could help me create a formula so that if I have a Text field, it can automatically add a “( )” around the first 2 or 3 digits? I know I could use the phone number field option but I rather not.

EX: 1301234901 --> (130)1234901 OR (21)301234901

So far this I’ve gotten the following but they’re applied in separate fields.

EX: “Field 1- Number”, Formula: LEFT({Number}, 3) As you’ll see by using this formula, it only isolates the 3 characters.

Ex: “Field 1- Number”, Formula: IF(LEFT({text number}, 3),"(") This only gives me the ‘parenthesis’ in return.

All help is appreciated :grinning:

[Justin_Barrett]

To combine multiple pieces into a single result, use the & operator between them. Some use the CONCATENATE() function instead, but & is more concise. I believe this formula will get you what you want:

IF(LEN(Number) > 2, "(" & LEFT(Number, 3) & ")" & MID(Number, 4, 50))

The first part checks to see if the number is longer than 2 digits, and will only build the result if that’s true. The rest is just adding different pieces together using the & operator: the open parenthesis, the first three digits, the closing parenthesis, and then everything from character 4 onward.

As a side note, the MID() function doesn’t care how many characters are actually in the string. I’m saying to grab 50 characters, but in reality, it will grab what it can up to the end of the string, and then stop automatically. There’s no need to precisely calculate how many characters are left.

[Coral_Hernandez]

Thank you so much for the help!

I made some adjustments to your formula and it worked, so happy! Many thanks!!
Formula I used: IF(LEFT({text number}, " “),”(" & LEFT({text number}, 2) & “)” & MID({text number}, 1, 50))

[Justin_Barrett · Accepted solution]

Coral_Hernandez:

IF(LEFT({text number}, " “),”(" & LEFT({text number}, 2) & “)” & MID({text number}, 1, 50))

That’s not a valid formula. The format of the LEFT() function is this:

LEFT(String, Count)

The second argument passed to the function is supposed to be a number, representing the number of characters that you want to pull from the left end of the string. At the beginning of your formula, the second argument you’re passing to LEFT() is a string containing a space. I’m not sure how Airtable accepts that, but in my test just of that piece alone, it returns the entire string, and any non-empty string is evaluated as True for the purposes of the IF() function.

If all you want is to check to see if there’s any value in the {text number} field before building the new output based on its pieces, this is the proper format for that test:

IF({text number}, ...

The other issue is with the MID function. The second argument passed to MID() is a number that tells Airtable where to begin grabbing the characters. By starting that latter part at character 1, you’re going back to the beginning of the string. This leaves you with the first two characters inside parentheses, and the full string—including those same first two characters again—after the parentheses. It looks like this (notice the repeating numbers underlined in red):

If you want to not repeat those characters, change the 1 in the MID() function to a 3.

In the end, the corrected formula looks like this, followed by its output:

IF({text number}, "(" & LEFT({text number}, 2) & ")" & MID({text number}, 3, 50))

[Coral_Hernandez]

Thanks so much for clarifying, I really appreciate it!