Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to delete Certain Characters in a excel 2010 cell

In column A I have a load of name that look like this

[John Smith]

I still want them in A but the [] removed...

like image 442
Hope T-p Avatar asked Jul 15 '13 10:07

Hope T-p


People also ask

How do I remove left 5 characters in Excel?

To delete the first or last n characters from a string, this is what you need to do: On the Ablebits Data tab, in the Text group, click Remove > Remove by Position. On the add-in's pane, select the target range, specify how many characters to delete, and hit Remove.

How do I remove left 3 characters in Excel?

1) In Number text, type the number of characters you want to remove from the strings, here I will remove 3 characters. 2) Check Specify option, then type the number which you want to remove string start from in beside textbox in Position section, here I will remove characters from third character.


3 Answers

If [John Smith] is in cell A1, then use this formula to do what you want:

=SUBSTITUTE(SUBSTITUTE(A1, "[", ""), "]", "")

The inner SUBSTITUTE replaces all instances of "[" with "" and returns a new string, then the other SUBSTITUTE replaces all instances of "]" with "" and returns the final result.

like image 118
jrad Avatar answered Sep 28 '22 11:09

jrad


Replace [ with nothing, then ] with nothing.

like image 28
István Hirsch Avatar answered Sep 28 '22 12:09

István Hirsch


Another option: =MID(A1,2,LEN(A1)-2)

Or this (for fun): =RIGHT(LEFT(A1,LEN(A1)-1),LEN(LEFT(A1,LEN(A1)-1))-1)

like image 24
zx8754 Avatar answered Sep 28 '22 11:09

zx8754