Reversing a String of numbers or text with delimiters

Phill032

New Member
Joined
Nov 9, 2016
Messages
38
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..
 

Phill032

New Member
Joined
Nov 9, 2016
Messages
38
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,191
Office Version
365
Platform
Windows
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?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,727
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:

Phill032

New Member
Joined
Nov 9, 2016
Messages
38
yes this is the result im looking for.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,727
this is not copy/paste solution but you can try

Code:
[SIZE=1]// Table1
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[/SIZE]
 

Phill032

New Member
Joined
Nov 9, 2016
Messages
38
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,191
Office Version
365
Platform
Windows
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. :)
 

horseyride

Board Regular
Joined
Nov 2, 2017
Messages
82
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:

Forum statistics

Threads
1,081,563
Messages
5,359,617
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top