Finding a specific value in a cell

**yr25** · 09-05-2012, 05:51 PM

I have column B which have many rows of data, for some reason the program I'm using doesn't like that I have a doube quote sign (")

I need to create a formula looking down all the rows in column B if any cell has a " in the field

Here's an example. Row B2 says: 36" BENT SB AXE HANDLE. I need to remove the " and it should just say 36 BENT SB AXE HANDLE.

Thank you

**nickmangan** · 09-05-2012, 05:55 PM

Hi

Try, =REPLACE(A1,3,1,"") Assuming A1 contains the text string above

Nick

**ajm123456789** · 09-05-2012, 06:07 PM

Or, highlight the affected cells. Click "Ctrl+F"
Click Replace.
Find what: "
Replace with: (leave this blank)
Then click Replace all

**twiggywales** · 09-06-2012, 03:41 AM

either do it as ajm said or this

=SUBSTITUTE($A1,CHAR(34),"")

where A1 has your initial wording. (the formula above will remove all instances of " within that text (its more complex as excel read "" as text so """ wouldn't subsitute " hence the reasoning behind CHAR(34) which is the charicter code for "