sandy666
03-22-2021, 08:36 AM
(question from the web (https://www.mrexcel.com/board/threads/power-query-make-new-column-by-extracting-a-word-from-a-cell-that-contains-a-sign.1165642/))
How do I extract from my column A a word that contains in it a $ sign.
For instance the data in column A would look something like
A1 = 5465 Apples$50 Twenty
A2 = 5687 Grapes$597 Three
answer:
B1 = Apple$50
B2 = Grapes$597
Apple$50 and Grapes$597 needs to be extracted to a new column of the same row.
SourceSourceResult
5465 Apples$50 Twenty5465 Apples$50 TwentyApples$50
5687 Grapes$597 Three5687 Grapes$597 ThreeGrapes$597
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.AddColumn(Source, "Result", each if Text.Select([Source],"$")="$" then Text.BetweenDelimiters([Source], " ", " ") else null)
in
Result
How do I extract from my column A a word that contains in it a $ sign.
For instance the data in column A would look something like
A1 = 5465 Apples$50 Twenty
A2 = 5687 Grapes$597 Three
answer:
B1 = Apple$50
B2 = Grapes$597
Apple$50 and Grapes$597 needs to be extracted to a new column of the same row.
SourceSourceResult
5465 Apples$50 Twenty5465 Apples$50 TwentyApples$50
5687 Grapes$597 Three5687 Grapes$597 ThreeGrapes$597
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.AddColumn(Source, "Result", each if Text.Select([Source],"$")="$" then Text.BetweenDelimiters([Source], " ", " ") else null)
in
Result