• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help converting numbers

TENCJS

New Member
I get spreadsheets that have have a list of one or two numbers and some with decimals. I need a faster way to convert the list to a three number format so I can sort numerically. Any suggestions?

Start
1719003748359.png


Format wanted
1719003838982.png
 
Power Query Solution

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Multiplied Column" = Table.TransformColumns(Source, {{"Size", each _ * 10, type number}}),
    #"Added Prefix" = Table.TransformColumns(#"Multiplied Column", {{"Size", each "0" & Text.From(_, "en-US"), type text}})
in
    #"Added Prefix"

Excel 2016 (Windows) 64 bit
A
B
C
D
E
1
SizeWidthSizeWidth
2
9.5​
w095w
3
9.5​
M095M
4
9​
w090w
5
9​
M090M
6
8.5​
w085w
7
8.5​
M085M
8
8​
w080w
9
8​
M080M
10
7.5​
w075w
11
7.5​
M075M
12
7​
w070w
13
7​
M070M
14
10​
M0100M
15
10​
w0100w
16
11​
M0110M
17
11​
w0110w
18
5​
M050M
19
5​
w050w
20
5.5​
M055M
21
5.5​
w055w
22
6​
M060M
23
6​
w060w
24
6.5​
M065M
Sheet: Sheet1
 
Back
Top