Reversing a String of numbers or text with delimiters

Phill032

Board Regular
Joined
Nov 9, 2016
Messages
51
Hi all, new to power Query and I need a solution to the above problem. basically I have a list of prices in 1 cell that are separated by a full stop "."
E.g. 10990. 10450. 10190. 9990
And so on up to a max of 19 changes.
I want to reverse these to show the below
9990. 10190. 10450. 10990
The reason for this is I want the last price change to show first for all rows so they align. Splitting the cell is the easy part so just need someone to help with the hard part..
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
sorry should also mention that each row has a different amount of changes so wanting the last Price change to align example of saw data below

10990. 9990
8990. 7990. 8490. 7490
12990
 
Upvote 0
Is there a particular reason you need to use Power Query for this?
What version of Excel are you using?
What would be the approximate maximum number of such prices in a single cell?
 
Upvote 0
is that what you want?

rawResult
10990. 99909990. 10990
8990. 7990. 8490. 74907490. 8490. 7990. 8990
1299012990

btw. delimiter in your example is ". " not only "."
 
Last edited by a moderator:
Upvote 0
this is not copy/paste solution but you can try

// Table1
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"raw", type text}}),
    Split = Table.SplitColumn(Type, "raw", Splitter.SplitTextByDelimiter(". ", QuoteStyle.Csv), {"raw.1", "raw.2", "raw.3", "raw.4"}),
    Demote = Table.DemoteHeaders(Split),
    Transpose = Table.Transpose(Demote),
    Sort = Table.Sort(Transpose,{{"Column1", Order.Descending}}),
    Transpose1 = Table.Transpose(Sort),
    Promote = Table.PromoteHeaders(Transpose1, [PromoteAllScalars=true]),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Promote, {{"raw.4", type text}, {"raw.3", type text}, {"raw.2", type text}, {"raw.1", type text}}, "en-GB"),{"raw.4", "raw.3", "raw.2", "raw.1"},Combiner.CombineTextByDelimiter(". ", QuoteStyle.None),"Result"),
    Replace = Table.ReplaceValue(Merge,".","",Replacer.ReplaceText,{"Result"}),
    Trim = Table.TransformColumns(Replace,{{"Result", Text.Trim, type text}}),
    Replace1 = Table.ReplaceValue(Trim," ",". ",Replacer.ReplaceText,{"Result"})
in
    Replace1
 
Last edited by a moderator:
Upvote 0
Sorry, my laptop wont let me reply... such is my life today.. I run reports for 12 dealerships. Just switched to excel 2013 from 2007.
The reason for power query is I can run many different queries from the 1 set of data. And also stack "apend" the data. All my previous reports have got bogged down with formulas and macros etc.
There would be a max of 19 price changes within 1 cell.
 
Upvote 0
The reason for power query is I can run many different queries from the 1 set of data. And also stack "apend" the data. All my previous reports have got bogged down with formulas and macros etc.
There would be a max of 19 price changes within 1 cell.
OK, thanks. I'll leave it to sandy666 &/or others then. :)
 
Upvote 0
Code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
MySort = (x) => Text.Combine(List.Reverse(Text.Split(x, ".")), ". "),
Reversed= Table.AddColumn(Source, "Reversed", each MySort([raw]))
in Reversed
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top