Forum Discussion
Extract 2 (or 1) decimal numbers from string
HI Anonymous ,
I can't find the regular in your text, it is hard to extract specific values from your records.
In my opinion, I can use power query formula to extract all numeric string(number code and decimal values) but can't accurate recognize which part you needed.
Maybe you can refer to following blog to replace other characters to extract numeric value:
Keep Only Numbers in Power Query
M query characters list who need to be removed:
clist= List.Transform({65..90,97..122,35,95}, each Character.FromNumber(_))
Regards,
Xiaoxin Sheng
Thank you for the reply. I managed to extract the first transaction with helper columns:
First trim the string and extract everything after the first EU#.
Then extract everything (the numbers I need) until the first ",".
Then extract the two numbers after that ",".
Concatenate the two in a new column.The last transaction is always at the end. So it is easy to extract the two numbers (the decimals of the transaction) until the second "," (e.g. 1000, "23").
Now I only need the body part of the transaction (e.g. "1000", 23). However, there is no consistent delimiter before that number. Is it possible to create a line in which the delimiter you seek is set up as anything except for a number? That would solve my problem.Hi Anonymous ,
>>Is it possible to create a line in which the delimiter you seek is set up as anything except for a number?
AFAIK, power query Split functions still not works on dynamic separator who recognize by its text characters. (auto recognize function will check your records and try to find out similar separators)
Regards,
Xiaoxin Sheng