change to 2 apostrophes

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
1,168
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
One of the programs that we have does not allow quotes inside of quotes but it does allow 2 apostrophes instead. Is it possible to Keep the quotes at the beginning and the end but any other inside change to 2 apostrophes?

"The man that went to the store said, "I feel good.""

"The man that went to the store said, ''I feel good.''"

Thanks in advance Stephen!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I know that this was not in the initial request but Andrew is there a way to modify this in case a phrase does not start out with quotes? i.e. Is the best

Current Formula returns
" the best"

need this!
"Is the best"
 
Upvote 0
Andrew, almost there.

"Is the best"
now comes out like
"Is the best''"
 
Upvote 0
How about:

=""""&MID(SUBSTITUTE(A1,"""","''"),3-(2*(LEFT(A1,1)<>"""")),LEN(A1)-(2*((LEN(A1)-LEN(SUBSTITUTE(A1,"""","")))=2)))&""""

It would have been easier if you had given a representative list of sample data in your original post.
 
Upvote 0
Here is a UDF (user defined function) that I think will do what you want...

Code:
Function Apostrophes(ByVal S As String) As String
  Dim X As Long, Quotes As Long
  If Left(S, 1) = """" Then S = Mid(S, 2)
  Quotes = (Len(S) - Len(Replace(S, """", "")))
  If Quotes Mod 2 = 1 Then
    S = Left(S, Len(S) - 1)
    Quotes = Quotes - 1
  End If
  S = Replace(S, """", "'")
  Apostrophes = """" & S & """"
End Function
If you are not familiar with installing UDF's, it is quite easy to do. Press ALT+F11 from any worksheet to go into the VB editor and, once there, click Insert/Module on its menu bar. Next, copy/paste the above code into the code window that just opened up. That's it... you are done. You can now use Apostrophes just like any other built-in Excel function. Go back to your worksheet and, assuming A2 contains your text, put this formula in B2...

=Apostrophes(A2)

This formula can be copied down and/or across as needed. Oh, and also, this function will handle multiple included quoted text within the given text string.
 
Last edited:
Upvote 0
Thanks guys for putting the time in on this. Rick Rothstein works great. I just changes the 1 line
S = Replace(S, """", "'")
to this
S = Replace(S, """", "''")

and it works great thank you both again!
 
Upvote 0
Thanks guys for putting the time in on this. Rick Rothstein works great. I just changes the 1 line
S = Replace(S, """", "'")
to this
S = Replace(S, """", "''")

and it works great
He-he... I misread your first message to be a typo... I thought your repeated the internal double-quotes by mistake... I did not see they were actually two apostrophes next to each other. I'm glad you saw what to change on your own.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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