Search code examples
excelformula

Adding Comma's to Strings in Excel 2016


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

Solution

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