change to 2 apostrophes

[Stephen_IV]

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!

 

[Andrew Poulsom]

How about?

=""""&MID(SUBSTITUTE(A1,"""","''"),3,LEN(A1))&""""

 

[Stephen_IV]

Andrew perfect. Thanks you!

 

[Stephen_IV]

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"

 

[Andrew Poulsom]

How about?

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

 

[Stephen_IV]

Andrew, almost there.

"Is the best"
now comes out like
"Is the best''"

 

[Andrew Poulsom]

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.

 

[Rick Rothstein]

Here is a UDF (user defined function) that I think will do what you want...

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.

 

[Stephen_IV]

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!

 

[Rick Rothstein]

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.