LinkBack URL
About LinkBacks
Hello,
I do sort Z-A (want to see the values from the lowest to the highest) but it just randomly sorts it and it is the same with all the columns.
How to fix this?
Hello,
I do sort Z-A (want to see the values from the lowest to the highest) but it just randomly sorts it and it is the same with all the columns.
How to fix this?
These are not numbers - they are text that looks like numbers. Fix that, and your columns will sort as you want them to.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Looks to me like all your values are actually text looking like numbers (numbers right-align, text left aligns). You need to convert them - is this an import from another program?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Yes, this is an import from an API.
Columns are categorized as General now, I made it to Number, problem still persists
Formatting does not change the underlying cell contents, it is only a cosmetic change. You will need to convert the data.
upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
so I just made new excel file and imported the data.
so try to sort any column either A-Z or Z-A and you will see that excel does it completely random.
If anyone could come up with a fix I would be very grateful
We have already told you why this is not sorting as expected.
To convert the TEXT numbers to real numbers, do this for each column (1 at a time)...
1. highlight the column
2. click Data tab/Text2Columns
3. click Next/Next/Finish
Repeat for all columns with numbers in them
You should now be able to sort
Select one of the columns - go to the data tab - select text to columns - click on next, next and finish without changing anything. Repeat for other columns.
Thank you guys! It helped!
Maybe you can explain me why it was like that and what this text to columns function does?
one more thing:
when query is refreshed, it goes back to text; any way to make excel automatically convert it to numbers after it refreshes the query?
It looks as though that is a Power Query/Get and Transform query result, in which case you should alter the column type in Power Query.
Don
Please remember to mark your thread 'Solved' when appropriate.
Could you please be more specific there?
I am not that advanced in excel unfortunately
Last edited by AliGW; 01-09-2018 at 07:42 AM.
Where are you importing the data from, and what are the steps you take to do so? In Power Query in 2010, when you select a column in the Query Editor, there is a dropdown at the top to select the data type for the column.
PQ column type.jpg
THank you, thank kind of helped. Excel treats it as numbers now and I am able to sort it.
However, when I transform it, even though I transform it to decimal number and hope to get some digits after the (.), it still gives me a whole number, and for example where it is a 0.091587 it would give me 91587
How to fix this?
Last edited by AliGW; 01-10-2018 at 02:07 AM. Reason: Unnecessary quotation removed.
Try one of the other options in the drop-down menu - decimal number or percentage.
Unfortunately does not work..Originally Posted by AliGW
What is the data source? It looks like you are running into a problem with your regional settings- do they use a comma as the decimal separator?
Data source - coinmarketcap.com
https://coinmarketcap.com/
Could you please be more specific with the regional settings and comma thing?
Normally in Excel would you enter one half as 0.5 or 0,5?
I would do it as 0.5
That's interesting. As far as I can see, the default settings for Lithuania in Windows are to use a comma as a decimal separator and a full stop as a grouping symbol. Have you changed your regional settings in Control Panel to something else, or have you told Excel to use different ones from your system settings? It looks like The Get and Transform engine is using the full stop as a grouping symbol, not a decimal point.
I have changed the setting in excel, was told to do so on this forum in order to get appropriate data on my other spreadsheet. (not using power query, but simply importing it from web: data>from web)
is there any way to make changes to this get and transform data so it would fit my current settings?
Or what should I do in order to achieve universal solution here?
I am just setting up a virtual machine with Lithuanian settings to test with. I'm guessing you don't want to simply alter your computer's regional settings in Control Panel, so it may be easiest to just do some string parsing in Power Query.
and what is this full stop as though?
Thank you for your help here!
OK, so the issue is definitely your regional settings which is what the PQ engine defaults to using. You need to edit the query to bring up the PQ window, then click the dropdown arrow to the left of the Home tab, choose Options and Settings, then Query options. Now choose Regional Settings in the Current Workbook section, and change it to English (United States) rather than Lithuanian. You should then be able to simply convert any number columns to the relevant type.
Man, thank you very much!!!
This problem has been haunting me for few months and nobody knew the solution to it!
Thank you, this was a tremendous help to me!
Wish you all the best!
You're welcome, and thank you for the rep.
could explain a little bit what is it and why do they need to make it so complicated? :D
so every region has their own approach to this decimal point thing?
Yes, different countries use different characters for decimal point and the thousands grouping. So most of Europe would write 1.234,00 whereas the UK and North America would write 1,234.00
ohh why make this so complicated, we should have universal solution to this :D
Microsoft does- they just assume everyone is American.
Thank you once again for your tremendous help here and informative answers.Microsoft does- they just assume everyone is American.
I have another issue though, maybe you will know the solution to this one as well.
I try to import API data from cryptopia.co.nz
They have publicly issued their APIs here:
https://www.cryptopia.co.nz/Forum/Thread/255
Lets say I choose to import this one:
https://www.cryptopia.co.nz/api/GetCurrencies
When I do that, I get this.
How to solve it?
Please start a new thread for this completely different issue.
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks