I am trying to create a formula to add a comma after the 2nd string if there are more name than two and otherwise add a comma after first string.
But my formula is just adding the comma after first string.
My formula:
=SUBSTITUTE(A2," ",", ",1)
My strings:
Strings
ARISMEL SANTOS ROSARIO
ADREMIR J GAMBOA ORTIZ
LOGAN NOVA HERNANDEZ MEJIA LACAYO
ADREMIR J HERNANDEZ MEJIA
AVERY GRAN
My unwanted result is:
ARISMEL, SANTOS ROSARIO
ADREMIR J, GAMBOA ORTIZ
LOGAN NOVA, HERNANDEZ MEJIA LACAYO
ADREMIR J, HERNANDEZ MEJIA
AVERY, GRANT
Assuming cell A1 has any of the example name you provided.
Put the following formula in B1 -> =LEN(A1) - LEN(SUBSTITUTE(TRIM(A1), " ", ""))
and the formula in C1 -> =SUBSTITUTE(TRIM(A1)," ",", ",IF(B1<=2,1,2))
It is good to have trimmed text (which I have done in formula) to begin with.
See if this helps.
Edit: If you want to do it in a single formula, =SUBSTITUTE(TRIM(A1)," ",", ",IF((LEN(A1) - LEN(SUBSTITUTE(TRIM(A1), " ", ""))) <=2,1,2))