cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lewispkelly
New Member

Adding numbers to a row in SmartSheets

Hi,

   I'm very new to Power Automate/Flows and I'm trying to build a Flow that takes a table from an Excel workbook and add it as new rows to a SmartSheets sheet. the issues I'm finding it the numbers in the table have a leading apostrophe added when copied in to SmartSheets. This means that SmartSheets can't use the information to create a chart as they are 'seen' as text.

the numbers are formatted as numbers in the Excel workbook and if I copy and paste the values manually, they are pasted as numbers.

I've tried to use the Float expression (the numbers have decimal points) when adding the values to SmartSheets but that gives me the same result. I did look at the data operation but I'm not really sure where to start.

Any advice on how to get the numbers to copy as numbers without the leading apostrophe will be greatly appreciated

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Lewispkelly,

 

Yes, the leading apostrophe (') is hidden, but if you using Get a sheet data in after insert the data into SmartSheet, the apostrophe won't appear, they just show without any apostrophe.

Screenshot 2020-11-09 163133.jpg

 

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

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-litu-msft
Community Support
Community Support

Hi @Lewispkelly,

 

I created a simple Flow to reproduce your issue, but not find the same problem, there is no leading apostrophe created in the SmartSheet:

Flow:

Screenshot 2020-10-31 151100.jpg

 Run result:

Screenshot 2020-10-31 151139.jpg

 

Could you please share an instance of Excel table and screenshots of Flow? They could help us assist you better.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-litu-msft 

   Thank you for replying and i'm sorry it's taken a while for me to get back to you. The information below is dummy data but is in the same format. I've tried to upload some images but I can't seem to get it to work so I'll have to try and explain. The top table below shows how the table in Excel Looks with the top row being set a table Headers, Column 1 formatted as text and the rest formatted as numbers

 

Column1APRMAYJUNJULAUGSEPOCTNOVDECJANFEBMAR
ACTUAL-0.300.090.300.090.080.25      
BUDGET0.020.200.250.350.500.600.750.801.200.901.301.50

 

The table below shows how the numbers look in Smartsheets after the flow has copied them across. When looking at the SmartSheet normally the leading apostrophe (') is hidden but shows when you click to edit the cell contents.

 

Column1APRMAYJUNJULAUGSEPOCTNOVDECJANFEBMAR
ACTUAL'-0.30'0.09'0.30'0.09'0.08'0.25      
BUDGET'0.02'0.20'0.25'0.35'0.50'0.60'0.75'0.80'1.20'0.90'1.30'1.50

 

the issue is that with the apostrophe SmartSheets see's the numbers as text and so won;t use them to generate charts which is the end point of this process. I've seen this before to maintain a leading zero for things like phone numbers but these numbers have a decimal so that shouldn't cause the apostrophe to appear. I've tried formatting the smartsheet incase it is seeing the decimal point as a thousand seperator but that isn;t it either. Aside from this there aren't any more options for formatting in SmartSheets.

 

in Power Automate I have tried to format the data as XML and JSON incase that was the issue but it was not. I've also tried using the float() expression to force the numbers as floating point numbers as they are all decimals but that didn't work either.

 

Any thoughts or suggestions would be greatly appricated.

 

Many thanks

Lewis

 

Hi @Lewispkelly,

 

Yes, the leading apostrophe (') is hidden, but if you using Get a sheet data in after insert the data into SmartSheet, the apostrophe won't appear, they just show without any apostrophe.

Screenshot 2020-11-09 163133.jpg

 

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

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Lewispkelly
New Member

Hi @v-litu-msft ,

   From the image you've shared I'm not sure how to implement that as the data is coming from an Excel spreadsheet and adding the data to a SmartSheets sheet.  I have implemented the second half of your solution with using the extra columns in SmartSheets to remove the leading apostrophe which has sorted out the issue. Thank you for your help with this. 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (32,949)