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

[gdddg]

Hi

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

Thank you

 

[gaz_chops]

Try

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

 

[gdddg]

It says formula you entered contains erors

 

[Momentman]

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
	A	B
1	"I am here]	"I am here"
2	james	james
3	"who am I	"who am I
4	"I need help]	"I need help"
Sheet9

Cell Formulas
Range	Formula
B1	=IF(AND(LEFT(A1,1)="""",RIGHT(A1,1)="]"),REPLACE(A1,LEN(A1),1,""""),A1)

 

[gdddg]

ADVERTISEMENT

THANK YOU!!!!!!

it works

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

TNX!!

 

[Momentman]

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]

ADVERTISEMENT

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]

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

 

[gdddg]

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]

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