Stripping quotation marks

[tucsondonpepe]

Hi,

I am trying to strip the quotation marks on a page.

I used the following in a subroutine, Range("A10") = Right(Range("A10"), Len(Range("A10")) - 1), to remove one quotation mark and received the following error message.

.

I would appreciate your help. Thank you.

Joe

 

[Bosquedeguate]

If the first and last characters are always the quotation marks the following formula will working in the cell with quotation marks removed.

=MID(A7,2,LEN(A7)-2) 'Assumes A7 contains a value like "=A2",

DateFormat.xlsm
	A	B
7	"=A2"	=A2
Sheet1

Cell Formulas
Range	Formula
B7	B7	=MID(A7,2,LEN(A7)-2)

 

[Bosquedeguate]

the SUBSTITUTE function could be used as well, it will replace all occurrences of the character to be changed or removed.

DateFormat.xlsm
	A	B
8	"=A2"xxxx"yyyy"	=A2xxxxyyyy
Sheet1

Cell Formulas
Range	Formula
B8	B8	=SUBSTITUTE(A8,CHAR(34),"")

 

[Bosquedeguate]

If your are doing this in VBA code you can use the MID or REPLACE function as well.

Before running Macro

DateFormat.xlsm
	A
1
2	12345
3	"=A2"
Sheet1

After running Macro

DateFormat.xlsm
	A
1
2	12345
3	12345
Sheet1

Cell Formulas
Range	Formula
A3	A3	=A2

Macro (Sub) VBA Code
Sub StripQuotes()
Dim str1
Dim rng As Range

Set rng = Worksheets("Sheet1").Range("A3")

str1 = Mid(rng.Value, 2, Len(rng.Value) - 2)
'or use Replace funtion below
' str1 = Replace(rng.Value, Chr(34), "")
rng.Value = str1
End Sub

 

[tucsondonpepe]

Thank you very much for the recommendations.

I also found this solution online,

Code

Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, MatchCase:=False

Joe