Skip to main content
Solved

AIRTABLE'S ANSWER TO EXCEL'S PROPER FUNCTION

P
Forum|alt.badge.img+9

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  

 

Best answer by TheTimeSavingCo

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

View original
Did this topic help you find an answer to your question?

4 replies

TheTimeSavingCo
Forum|alt.badge.img+28

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

Adam, Consultant @ thetimesaving.company
P
Forum|alt.badge.img+9
  • Project_NP_LS
  • Author
  • Known Participant
  • 26 replies
  • July 11, 2024
TheTimeSavingCo wrote:

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

 

 

TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo
TheTimeSavingCo
Forum|alt.badge.img+28
Project_NP_LS wrote:

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

Adam, Consultant @ thetimesaving.company
P
Forum|alt.badge.img+9
  • Project_NP_LS
  • Author
  • Known Participant
  • 26 replies
  • July 12, 2024
TheTimeSavingCo wrote:

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

TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo

Reply


Most helpful members this week

Terms & Conditions