Stripping quotation marks

tucsondonpepe

New Member
Joined
Jan 30, 2022
Messages
43
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi,

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

1689367686611.png


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.

1689367885763.png
.

I would appreciate your help. Thank you.

Joe
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
AB
7"=A2"=A2
Sheet1
Cell Formulas
RangeFormula
B7B7=MID(A7,2,LEN(A7)-2)
 
Upvote 0
the SUBSTITUTE function could be used as well, it will replace all occurrences of the character to be changed or removed.

DateFormat.xlsm
AB
8"=A2"xxxx"yyyy"=A2xxxxyyyy
Sheet1
Cell Formulas
RangeFormula
B8B8=SUBSTITUTE(A8,CHAR(34),"")
 
Upvote 0
Solution
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
212345
3"=A2"
Sheet1


After running Macro
DateFormat.xlsm
A
1
212345
312345
Sheet1
Cell Formulas
RangeFormula
A3A3=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
 
Upvote 0
Thank you very much for the recommendations.

I also found this solution online,

Code

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

Joe
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top