Convert Smart or Curly Quotes to Straight

dschaefer

We use a Smart Sheet to track new product development requests from our account mangers through engineering.  At the end of the process, we need to transfer the information into NetSuite, our ERP system.

The problem is that the data gets copied in from a variety of sources and sometimes includes curly quotes instead of the standard straight ones (see the attached screen shot).  The curly quotes generate odd characters when imported into NetSuite.

Is there a way to force SmartSheet to convert all quotes to straight quotes to avoid this?

Paul Newcome

Have you tried using a Find and Replace?

dschaefer

I have, and it does work, but as new data comes in I'd need to do it repeatedly.  I'm hoping for an automated solution.

It also means that i have to find an instance of each curly quote to copy and paste since typing a quote in the find and replace box creates a straight quote.

 

Andrée Starå

Hi,

You could maybe use the SUBSTITUTE function.

https://help.smartsheet.com/function/substitute

Would that work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

Actually. I don't think the Substitute is possible. I tried playing around with it and any appearance of a smart quote in a formula totally crashes the formula. #UNPARSEABLE. Even when I surrounded it with basic quotes to call it text. I wonder if there is a way to convert the data in the transfer to the ERP. How are you getting the data in there? 

Andrée Starå

@Mike,

EDIT: Reading it again SUBSTITUTE should work because it's not a formula as I understand it.

My thinking was that the formula shouldn't be activated until updated. SUBSTITUTE and then find/replace but I agree that if the conversion can happen between Smartsheet and ERP that would probably be the simplest.

Mike Wilday

@Andree I was thinking substitute would work to replace the smartquotes, not after the find and replace. I wonder if the new unicode function could be used to replace smart quotes in the formula... gonna keep playing around with this one... 

 

Andrée Starå

@Mike,

Great idea, but I don't think it will work if the formula is "active." There's a lot to experiment with the new formulas.