Find cells that begin with specific symbol and end with specific symbol

gdddg

New Member
Hi

I need to find all the cells that begin with symbol " and end with symbol ] and replace the ] with the ".

Thank you

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

gaz_chops

Well-known Member
Try

=IF(AND(LEFT(A1,1)="""",RIGHT(A1,1)="]"),SUBSTITUTE(A1,RIGHT(A1,1),""""),A1)

gdddg

New Member
It says formula you entered contains erors

Momentman

Well-known Member
You can also do it with the FIND and REPLACE dialog
1. Select the column that has the data or maybe the whole sheet
2. Open the Find and Replace dialog (CTRL+F)
3. In the FIND what type "*], then choose FIND ALL
4. Then select all the returned cells at the bottom of the FIND dialog

that way you have only the cells that start with a quote and end with "]" selected.
5. Move over to the replace tab of the Find and replace dialog
6. In find type ], in the replace with, type "

Well, looks a little more complex, but i kinda prefer it to create another column t effect the changes and then copy/paste values.....

Formula could be

Excel 2013
AB
1"I am here]"I am here"
2jamesjames
3"who am I"who am I
4"I need help]"I need help"
Sheet9
Cell Formulas
RangeFormula
B1=IF(AND(LEFT(A1,1)="""",RIGHT(A1,1)="]"),REPLACE(A1,LEN(A1),1,""""),A1)

Last edited:

gdddg

New Member
THANK YOU!!!!!!

it works

You just saved me from couple of hours od manualy corecting the tables....

TNX!!

Momentman

Well-known Member
It says formula you entered contains erors
I guess you didn't paste the formula correctly. I used the formula and it gives exact results as mine

István Hirsch

Well-known Member
Give this a try too:

=IF(COUNTIF(A1,"""*]")=1,SUBSTITUTE(A1,"]",""""),A1)

If you are going to use what Momentman suggested, after „Find All” just press Ctrl + A to select all the desired cells.

gdddg

New Member
it is posible, i'm not that good with excel and formulas....
Once again, thank you both!

gdddg

New Member
ok, got to ask, if i want to use the fomula on the row C, i replace all the A with C in formula?

gaz_chops

Well-known Member
ok, got to ask, if i want to use the fomula on the row C, i replace all the A with C in formula?

Yes

Replies
3
Views
292
Replies
4
Views
247
Replies
2
Views
362
Replies
2
Views
204
Replies
6
Views
172

1,195,834
Messages
6,011,866
Members
441,651
Latest member
drewe2000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back