UC San Diego SearchMenu

Problem

Your source data has unwanted characters at the beginning or end.  It may have come that way from a provider or it may be the result of concatenating cells.

Solution

Remove both leading and trailing characters but preserve that character if it is a delimiter:

=SUBSTITUTE(TRIM(SUBSTITUTE(A2,"[removeLeadingTrailing]"," "))," ","[delimiterBetween]")

Specific examples:

comma
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,","," "))," ",",")

period
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,"."," "))," ",".")

semicolon+space
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,"; "," "))," ","; ")

space
=SUBSTITUTE(TRIM(SUBSTITUTE(A2," "," "))," "," ")