Forum Discussion
Extract number values from column
I have data in a column (called "Cost") that looks like this:
110DMS
45PRED
GLS25
LT75
I want it to be this:
110
45
25
75
I need to extract only the numbers within this mixed column, but I can't figure it out. There are other messages and blogs similar to this, but not quite what I need. The closest I've found is to create a custom column with a formula similar to:
List.First(List.Select(Text.Split([Cost], "-"), each Number.Mod(try Number.From(_) otherwise null,1)=0))
BUT, I don't have a seperator. There isn't a dash (-) or anything specific about where the number is located, other than the fact that it's generally before our after letters.
Any help is truly appreciated.
Thanks!
Anonymous
Hi, looks this link:
http://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/
6 Replies
Anonymous
Hi, looks this link:
http://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/
I tried out the ExcelGuru Link above, and it worked like a charm! Assuming you already have your data source setup, you can use the advanced query editor to add the cleansing steps.
1. To the beginning, add your variable:
(LET)
CharsToRemove = List.Transform({32,46,33..45,47,58..126}, each Character.FromNumber(_)),
2. At the end of your steps, add the following:
First, Add comma to the last line of your existing M query, before "IN", so it knows to continue when it gets to that line...
#"Changed TypeX" = Table.TransformColumnTypes(#"Previous M Step name",{{"Field to Convert", type text}}),
#"Added CustomX" = Table.AddColumn(#"Changed TypeX", "New Field Name", each Text.Remove([Field to Convert],CharsToRemove))
in
#"Added CustomX"That should work, just swap out the bold text with your field names!
That was it! You know, I saw that blog article and it looked more complicated than what I thought I needed, but after you pointed me in that direction, I took my time with it, and it worked!
FYI: For others who have the same request, here's what my query looks like:
let CharsToRemove = List.Transform({32,46,33..45,47,58..126}, each Character.FromNumber(_)), Source = Folder.Files("F:\Data\Testdata"), #"Combined Binaries" = Binary.Combine(Source[Content]), #"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=40, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Added Custom" = Table.AddColumn(#"Imported CSV", "Cost", each Text.Remove([Mycolumnname],CharsToRemove)) in #"Added Custom"
we don't have power query as an option - so we've needed to be a little more manual.
our scenario only went to a max of ten characters. it's a little messy and I'm sure that the ISERROR isn't the best as far as performance goes.
I'm sure there will be many out there screaming NO!! at this :)
VAR Key1 =
MID ( TableName[Cost], 1, 1 )
VAR Key2 =
MID ( TableName[Cost], 2, 1 )
VAR Key3 =
MID ( TableName[Cost], 3, 1 )
VAR Key4 =
MID ( TableName[Cost], 4, 1 )
VAR Key5 =
MID ( TableName[Cost], 5, 1 )
VAR Key6 =
MID ( TableName[Cost], 6, 1 )
VAR Key7 =
MID ( TableName[Cost], 7, 1 )
VAR Key8 =
MID ( TableName[Cost], 8, 1 )
VAR Key9 =
MID ( TableName[Cost], 9, 1 )
VAR Key10 =
MID ( TableName[Cost], 10, 1 )
VAR MyNumber =
IF ( ISERROR ( VALUE ( Key1 ) ) = FALSE (), Key1, "" )
& IF ( ISERROR ( VALUE ( Key2 ) ) = FALSE (), Key2, "" )
& IF ( ISERROR ( VALUE ( Key3 ) ) = FALSE (), Key3, "" )
& IF ( ISERROR ( VALUE ( Key4 ) ) = FALSE (), Key4, "" )
& IF ( ISERROR ( VALUE ( Key5 ) ) = FALSE (), Key5, "" )
& IF ( ISERROR ( VALUE ( Key6 ) ) = FALSE (), Key6, "" )
& IF ( ISERROR ( VALUE ( Key7 ) ) = FALSE (), Key7, "" )
& IF ( ISERROR ( VALUE ( Key8 ) ) = FALSE (), Key8, "" )
& IF ( ISERROR ( VALUE ( Key9 ) ) = FALSE (), Key9, "" )
& IF ( ISERROR ( VALUE ( Key10 ) ) = FALSE (), Key10, "" )
RETURN
IF(MyNumber = "", BLANK(), VALUE(MyNumber))Thanks for the info Dog!
Hello all,
I'm new to Power Query (M) code, and i can't quite find on forums what i need. It's quite similar to the original post, but not the same.
I have a column that contains letters, numbers, signs (basically all characters are possible) that looks like this (cell data is between quote marks ""):
A1: "Lorem ipsum dolor sit amet, consectetur adipiscing elit. 100 Mauris ac facilisis eros. Fusce congue venenatis eros sit amet mattis. Mauris tempus turpis quis iaculis dictum. 1500 Cras mollis, velit at finibus blandit, sapien erat commodo velit, eget tempor tellus ex ut elit. Proin magna est, venenatis sagittis quam sed, sodales viverra nisl. Morbi in eros sollicitudin, placerat erat ut, elementum neque 1001234567"A2: "1009876543Lorem ipsum dolor sit amet, consectetur adipiscing elit. 100 Mauris ac facilisis eros. Fusce congue venenatis eros sit amet mattis. Mauris tempus turpis quis iaculis dictum. 1500 Cras mollis, velit at finibus blandit, sapien erat commodo velit, eget tempor tellus ex ut elit. Proin magna est, venenatis sagittis quam sed, sodales viverra nisl. Morbi in eros sollicitudin, placerat erat ut, elementum neque"
What i want is to extract the number 1001234567 from the first cell and 1009876543 from the second cell, etc..
There are constants that i have noticed in the text:
- The number that i want extracted always starts with 100,- It's always 10 digit long.
The problem is it can be at any place in the cell and the number 100 can occur multiple times in the cell.
I'm able to do this partially through excel formulas, but as i seen in Power Query, i can do so much more with it, but i don't know how.
Can someone help me out?
