Skip to main content

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  

 

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

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


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

 

 

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

 

 


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")

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")


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. 👍

Reply


Terms & Conditions