Forum Discussion
Extract number values from column
- 9 years ago
Anonymous
Hi, looks this link:
http://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/
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?