Combine First Name and Last Name, lowercase letters, with capital initials only; No double spaces

[ADEFE_EUROPA]

TRIM(UPPER(LEFT([Nome], 1)) & LOWER(RIGHT([Nome], LEN([Nome]) - 1)) & " " & UPPER(LEFT(SPLIT([Sobrenome], " ")[1], 1)) & LOWER(RIGHT(SPLIT([Sobrenome], " ")[1], LEN(SPLIT([Sobrenome], " ")[1]) - 1)) & " " & UPPER(LEFT(SPLIT([Sobrenome], " ")[2], 1)) & LOWER(RIGHT(SPLIT([Sobrenome], " ")[2], LEN(SPLIT([Sobrenome], " ")[2]) - 1)) & " " & UPPER(LEFT(SPLIT([Sobrenome], " ")[3], 1)) & LOWER(RIGHT(SPLIT([Sobrenome], " ")[3], LEN(SPLIT([Sobrenome], " ")[3]) - 1)))

ERROR:  "LEN function is used incorrectly"

Good morning distinguished - HEELLLP

I'm trying to unify some expressions to get the simple result:

[Name] = DAVID RObson
[Surname] = PEREIRA santoS

EXPECTED RESULT = David Robson Pereira Santos

1. And unify the two columns, First Name and Last Name;
2. Put the entire name in lowercase letters, with only the initials capitalized;
3. Eliminate double spaces between names;

[TeeSee1]

I do not know about other AppSheet developers. But I find AppSheet's string/array manipulation capability very weak (fixed number of elements maybe doable but variable number of elements are impossible to handle) and I would use an Apps Script to handle something like this. It only takes a few lines of code and you can handle as many sub names as you wish. (i.e. First Name contains 'aAa  bBb   ccC')

This is just an example code ..

function formatName(name) {
  let arName = name.trim().replace(/\s+/g, ' ').split(" ");
  let result = '';
  arName.forEach((e)=>{
    let er = e.toLowerCase().replace(e.charAt(0),e.charAt(0).toUpperCase())
    result = result + ' ' + er;
  })
  return result.trim();
}

Spreadsheet formula could be another option although not as powerful as Apps Script..

[ADEFE_EUROPA]

Dear TeeSee1 Thank you very much! It's very kind of you to help me!

However, it wasn't exactly a script I wanted, I would really need a simple Appshet expression to solve this:

Is there any viable logic that could offer me the expected result?

Thanks

[Suvrutt_Gurjar · Accepted solution]

I totally agree with @TeeSee1 that a spreadsheet expression or GAS may be a better option. I believe the following expression and some other compact variants of it will work. However they are not likely to be much more compact. With such requirements , there could be variations such as first name or surname is just one string or more than two strings. The below will work for first name and surname both having up to two strings as per your given example.

Long expressions such as below are more for academic purpose rather than recommended to be used. Best is to use spreadsheet or GAS approach in this case.

CONCATENATE(
UPPER(LEFT(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),1),1)),
LOWER(MID(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),1),2,(LEN(UPPER(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),1)))-1))
), " ",

UPPER(LEFT(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),2),1)),
LOWER(MID(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),2),2,(LEN(UPPER(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),2)))-1))
) , " ",
UPPER(LEFT(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),3),1)),
LOWER(MID(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),3),2,(LEN(UPPER(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),3)))-1))
)," ",
UPPER(LEFT(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),4),1)),
LOWER(MID(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),4),2,(LEN(UPPER(INDEX(SPLIT( CONCATENATE([Nome] , " ", [Sobrenome])," "),4)))-1))
)
)

[ADEFE_EUROPA]

Thank you very much dear Suvrutt_Gurjar

I tweaked it and it turned out really well!
Thanks for your brilliant help!

TRIM(CONCATENATE(
UPPER(LEFT(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 1), 1)),
LOWER(MID(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 1), 2, LEN(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 1)) - 1))
& " " &
UPPER(LEFT(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 2), 1)),
LOWER(MID(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 2), 2, LEN(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 2)) - 1))
& " " &
UPPER(LEFT(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 3), 1)),
LOWER(MID(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 3), 2, LEN(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 3)) - 1))
& " " &
UPPER(LEFT(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 4), 1)),
LOWER(MID(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 4), 2, LEN(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 4)) - 1))
& " " &
UPPER(LEFT(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 5), 1)),
LOWER(MID(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 5), 2, LEN(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 5)) - 1))
& " " &
UPPER(LEFT(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 6), 1)),
LOWER(MID(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 6), 2, LEN(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 6)) - 1))
& " " &
UPPER(LEFT(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 7), 1)),
LOWER(MID(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 7), 2, LEN(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 7)) - 1))
& " " &
UPPER(LEFT(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 8), 1)),
LOWER(MID(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 8), 2, LEN(INDEX(SPLIT(CONCATENATE([Nome], " ", [Sobrenome]), " "), 8)) - 1))
))

[Suvrutt_Gurjar]

Hi @Roderick , @Arthur_Rallu , @lizlynch 

Request to implement PROPER() function for proper case in AppSheet. It would be handy in many use cases such as above. We already have UPPER(), LOWER(). We also have INITIALS() that capture the initials letters of each word of a text string. So implementing PROPER() could be a natural progression.

There is a feature request for the same.

Update feature auto change text in column to lower... - Google Cloud Community

[lizlynch]

Thank you for the feature suggestion, @Suvrutt_Gurjar! I've passed this feedback on to the team.

[Suvrutt_Gurjar]

Thank you @lizlynch