Removing special characters

Jennifer Kaupke

Hello!

I am importing from excel and ending up with extra characters in the cell. For example, instead of the cell displaying:

Spanish, Portugues

I am instead seeing:

["Spanish","Portugues"]

Is there a formula that would remove the [ ] " from the cell?

Thank you!

Carson Penticuff

This should do the trick:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([YOUR COLUMN]@row, ",\"", ", "), "\"", ""), "[", ""), "]", "")

Carson Penticuff

=SUBSTITUTE(SUBSTITUTE([YOUR COLUMN]@row, "[", ""), "]", "")

Replace the column name as needed. This assumes that all entries are in the same column.

Jennifer Kaupke

@Carson Penticuff Your formula removed the [ ] and resulted in:

"Spanish","Portuguese"

Is it possible to remove all of the " " to result in:

Spanish, Portuguese

Carson Penticuff

This should do the trick:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([YOUR COLUMN]@row, ",\"", ", "), "\"", ""), "[", ""), "]", "")

Jennifer Kaupke

@Carson Penticuff This should do the trick:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([YOUR COLUMN]@row, ",\"", ", "), "\"", ""), "[", ""), "]", "")

____________________

This is perfect, Thank you!!!!

Paul Newcome

@Carson Penticuff I believe you can use CHAR(34) in place of quotes instead of the backslash. Gets rid of one of the SUBSTITUTE functions (and can be used to insert quotes in a text string output as well). Might be helpful in the future.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([YOUR COLUMN]@row, "[", ""), "]", ""), CHAR(34), "")

Carson Penticuff

@Paul Newcome Thanks, Paul. I didn't think about using CHAR() to avoid having to escape the quotes. I added the extra SUBSTITUTE() to preserve the space after the comma as in Jennifer's example. Learning something new every day!