How to delete Certain Characters in a excel 2010 cell

Question

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

[John Smith]

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

`SUBSTITUTE( text, old_text, new_text, [nth_appearance] )` i.e `=SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]","")` — Skip Intro, Jul 15 '13 at 11:33

score 27 · Answer 1 · answered Jul 15 '13 at 13:01

27

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.

answered Jul 15 '13 at 13:01

jrad

3,172
3
22
24

If I want to remove only one occurrence? – Igor Fomenko Apr 21 '18 at 19:00

score 2 · Answer 2 · answered Jul 15 '13 at 12:58

2

Replace [ with nothing, then ] with nothing.

answered Jul 15 '13 at 12:58

István Hirsch

122
3

This would be my answer too. It is faster than any of the answers above. – joehua Nov 06 '16 at 23:00

zx8754 · Answer 3 · 2013-07-15T13:35:22.407

1

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)

edited Jul 15 '13 at 13:35

answered Jul 15 '13 at 13:28

zx8754

52,746
12
114
209

How to delete Certain Characters in a excel 2010 cell

3 Answers3

Linked