Apostrophe being inserted before number when importing from web form

Apostrophe being inserted before number when importing from web form

I am using MS Forms to collect information and using an integration through Flow to insert responses into Smartsheet. Answers that require a # (I.E. salary or years experience) are being inserted into Smartsheet with an ' before the number which results in my formulas not working. I need to find a way to make this work without manually removing the ' from each row. 

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Lindsay,

    It's because the information gets added to Smartsheet as text. Can you change it in Microsoft Flow before it's sent to Smartsheet?

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You could create helper columns and use a VALUE(SUBSTITUTE(........, "'", "")) to remove the apostrophe and convert it into a numerical value.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Paul,

    Yes, I thought about that as well but if MS Flow is like Zapier it could be easier to format the data before it's sent to Smartsheet. No need for helper columns if that's the case. 

    But, whatever works! ?

     

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Oh. Well ok then. I had no idea what MS Flow was, so I just went with what I knew which is handling the data after it comes into SS. Haha.

     

    I'm actually rather technologically illiterate. I only know SS as well as I do because I've had to use it so much. Hahaha

  • This worked great!!!

    I also tried to fix on the front end using Flow but the coding was above me. 

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Haha! wink

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Excellent!

    The important thing is that you got it working!

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

  • If you use the "text to columns" tool on the Data tab, prior to importing your data into Smartsheet, the number should not have an apostrophe before it once in Smartsheet.


    In Excel:

    1. Choose the Data tab atop the ribbon.
    2. Select the column with numbers
    3. Select Text to Columns.
    4. Ensure Delimited is selected and click Next.
    5. Clear each box in the Delimiters section and instead choose Comma and Space.
    6. Click Finish.


Sign In or Register to comment.