I have an issue with copying data from my Sales order page to Excel.
We have been doing this for years but only recently (Maybe around 3 months-ish that we keep noticing Paragraph Returns (At least we think it might be a Return) on two columns of numbers (quantity and unit price) when pasting onto Excel Sheets.
There is a symbol in front of the numbers, an arrow up which turn right. The spreadsheet now no longer recognise these as numbers even though it’s showing fine and therefore formulas cannot be used.
We have tried using the Excel replace function with no luck and can only manually delete the return by pressing delete 3x times before the numbers. Which gets very difficult with large amount of data.
I am using the Cloud version of Manager 21.10.19 on Google Chrome Version 94.0.4606.61 (Official Build) (x86_64) with Macbook Air 2020 and Microsoft Excel for Mac version 16.53. I have tried pasting on Google sheet as well as on Microsoft Excel on Windows as well. They all return the same result.
Attached are images of the original sales order page and the result on Excel under qty and original price it’s showing this symbol and columns beside of the right using these referenc numbers are not recognising them as numbers.
Please let me know if this issue can be fixed or if you need more information.
You are copying from a rendering of HTML code, and from a custom theme display on top of that. This is a not a feature of the program. You cannot expect that to go well. What you describe (you don’t actually illustrate it) sounds like formatting characters. Notice that these are the only columns in your spreadsheet that are not left-justified. So this is a result of how you have pasted into your spreadsheet and/or your spreadsheet cell formatting. Manager cannot resolve this issue.
Thanks Tut. I’ve tried pasting in different ways (as values, as keeping source formatting) to different and new spreadsheet but it’s all resulting the same way. So I’m not sure how to change the format.
It’s not left justified when I pasted it unless I go in and press back space 3x times on each cell. So there seems to be 3x extra space in front of each numbers which is not recognised even when I try to use the replace function to replace “space” with “blank”.
Is there a way to copy this data to a spread sheet not from HTML code? What can I change in the custom theme display?
There is no “copy to clipboard” feature for the Sales Order unfortunately.
I have tried Value() or NumberValue() and as well as =VALUE(TRIM(CLEAN()))
as well as this one from the discussion you linked =NUMBERVALUE(RIGHT(AE4,LEN(AE4)-SEARCH(“$”,AE4,1)),“.”,“,”)
They didn’t work. But I will do more research based on what you have pointed out in converting text to numbers.
Yes I think this sounds like what I’m having issues with. I will do more research on google, I’m not well versed on coding. Thank you for pointing this out.
That depends on what you are really trying to do. In other words, why are you copying from the display of a sales order presented in a custom theme? By doing that, you have gone to the end of a fairly long chain rather than to the source of the data. This is something like copying a picture of the data instead of the data itself.
From your first screen shot, it looks like you are trying to update prices. That might be easier to accomplish using Batch Update.
This is an example using Sublime Text, but there are plenty of other text editors that will do the same thing. Here all the grey dashes represent tab spaces and the grey dots represent normal spaces, so I can see I don’t have any other unwanted hidden characters messing up the formatting.
I frequently use this method to copy from purchase invoices for calculating my pricing in a spreadsheet. I use the text editor as an intermediate step to clean up the formatting, using regular expressions in find-and-replace operations to add and remove tab spaces and move data around so that when I paste into the spreadsheet all the columns are set up according to how I use them there.
Yes I did this sorry I forgot to mention the cell numbers.
I added the appropriate cell number to the formula but it was still coming up with #value! error.
Thanks, we use a floating price based on the gold price of the day. I also need to show the base price, the added surcharge on top and then the final unit price for the customers. So we cannot batch update all our inventory everyday and we have over 9000 items in our inventory.
Thank you Graham I just downloaded and tried this. I replaced /t with blank which worked but I’m not sure how to then copy and paste from sublime to Excel without losing the column formats and messing up the data.
Or is there a way to paste into Sublime with columns intact? and keep selections only on certain columns for the find and replace functions?
Sorry I don’t know enough about Sublime or coding to use this efficiently but willing to learn.
Mac OS comes with its own text editor. I haven’t used Mac OS in several years, so I can’t remember what it’s called or what functionality it has. But for the basic operations you should be able to use that instead of downloading new software like Sublime Text.
Tabs are commonly used as column separators. When pasting data into a spreadsheet program like Excel, it usually converts tab spaces into column breaks. So don’t remove or replace the tabs unless there are some that are creating columns you don’t want. You may have extra options when pasting into Excel. In some programs, for example, you can choose Paste Special, and you might be given options to specify what character you would like to use as your column separator. But in this case you shouldn’t need to do that.
What I would do is the following:
Copy your data from Manager.
Open a text editor like Sublime Text or whatever comes with Mac OS. Paste your data here. Text editors do not store text attributes like font, colour, size, alignment, etc, so just by doing this step you might be getting rid of unwanted formatting.
Select all the text and copy it.
Paste it into Excel.
If this hasn’t worked, I would go back to the text editor and look more closely at the data where you have problems. Show hidden characters. In Sublime Text you can do this by selecting all the text. In other programs you might have a menu item like View > Show hidden characters.
If you can see anything other than tabs and spaces between your words and numbers, you may need to strip those out. You should be able to do this with a Find-Replace operation. Regular Expressions (RegEx) enable you to do some quite complex searches and replacements, but if it’s just a single problematic character that you need to remove then they shouldn’t be required.
If you try the above and it still isn’t working then post a screenshot of what you see in your text editor with the hidden characters visible.
