Menu
In column A I have a load of name that look like this
[John Smith]
I still want them in A but the [] removed...
442
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.
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.
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.
118
Replace [ with nothing, then ] with nothing.
28
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)
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
