AIRTABLE'S ANSWER TO EXCEL'S PROPER FUNCTION

[Project_NP_LS]

Hello AT Community, 

I am looking for a formula that capitalises the first letter of every word before the " |"  in a string. 3 examples below

EXAMPLE 1

The output for "ARCHITECT | Tom Jerry” 

to be Architect

EXAMPLE 2

The output for "BUILDING AUTHORITY OFFICER | Road Runner" 

to be Building Authority Office

EXAMPLE 3

The output for "CIVIL ENGINEER: Energy Performance Certificate | Bugs Bunny", 

to be Civil Engineer: Energy Performance Certificate 

Thanks in advance  

[TheTimeSavingCo · Accepted solution]

Try this:

REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(
LOWER(
    LEFT(
      {Name},
      FIND(
        ' | ',
        {Name}
      )
    )
  )
, "^a| a", " A"), "^b| b", " B"), "^c| c", " C"), "^d| d", " D"), "^e| e", " E"), "^f| f", " F"), "^g| g", " G"), "^h| h", " H"), "^i| i", " I"), "^j| j", " J"), "^k| k", " K"), "^l| l", " L"), "^m| m", " M"), "^n| n", " N"), "^o| o", " O"), "^p| p", " P"), "^q| q", " Q"), "^r| r", " R"), "^s| s", " S"), "^t| t", " T"), "^u| u", " U"), "^v| v", " V"), "^w| w", " W"), "^x| x", " X"), "^y| y", " Y"), "^z| z", " Z")

  &
RIGHT(
  {Name},
  LEN(
    {Name}
  ) - 
  FIND(
    " | ",
    {Name}
  )
)

Regex is from here

[Project_NP_LS]

Hello there, 

Apologies for the delayed/late reply. This is perfect and helped me get to where I wanted. Thank you very much indeed. I actually had to wrapped your formula in a this one, LEFT(Your Regex Formula, FIND("|", {Name}) - 1) order to get the desired outcome. The reason being, your formula outputted 'Architect | Tom Jerry'. So by putting your formula in the one above, I got just Architect which is exactly what I was after. Thank you again for the Regex formula. Much appreciated

[TheTimeSavingCo · Accepted solution]

Ah, sorry about that!  If you delete "RIGHT" section it'll do what you needed, apologies:

REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(
LOWER(
    LEFT(
      {Name},
      FIND(
        ' | ',
        {Name}
      ) - 1
    )
  )
, "^a| a", " A"), "^b| b", " B"), "^c| c", " C"), "^d| d", " D"), "^e| e", " E"), "^f| f", " F"), "^g| g", " G"), "^h| h", " H"), "^i| i", " I"), "^j| j", " J"), "^k| k", " K"), "^l| l", " L"), "^m| m", " M"), "^n| n", " N"), "^o| o", " O"), "^p| p", " P"), "^q| q", " Q"), "^r| r", " R"), "^s| s", " S"), "^t| t", " T"), "^u| u", " U"), "^v| v", " V"), "^w| w", " W"), "^x| x", " X"), "^y| y", " Y"), "^z| z", " Z")

[Project_NP_LS]

Thank you very much, this is absolutely perfect. Shame Airtable don’t have a PROPER function like in excel, but I guess we can’t have everything. Thanks again. 👍🏾