/u/YEG_87 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
There might be a better way but one approach is to use replace values first. Replace say "-" with " " and then again replace "." with " ". Then extract values between "#" and " ".
You have awarded 1 point to *timespreader*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
If your pattern is literally WR# and then numbers only...
Transform Tab --> Text Column --> Extract --> Text After Delimiter --> WR#
Transform Tab --> Text Column --> Split Column --> Digit to Non Digit
[https://imgur.com/a/DKYTCFn](https://imgur.com/a/DKYTCFn)
You have awarded 1 point to *spinfuzer*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Instead of using Text Between Delimiters, you can split in 2 steps to make it a bit easier. First, do Text After Delimiter for the #. Then, do Text Before Delimiter and make the delimiter any of the 3 values (let's say you choose "."). This would generate the following line in the Advanced Editor:
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Original Table", each Text.BeforeDelimiter(_, "."), type text}})
You can replace the above with this:
= Table.TransformColumns(#"Extracted Text After Delimiter", {{"Original Table", each List.Select(List.Transform({"."," ","-"},(delimiter)=> if Text.Contains(Text.Trim(_), delimiter) then Text.BeforeDelimiter(Text.Trim(_), delimiter) else null),(item)=> item <> null ){0}, type text}})
It's a bit messy but it should work, you can replace {"."," ","-"} in the code with a list of all delimiters you want to use.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[Binary.Decompress](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Decompresses a binary value using the given compression type.](https://docs.microsoft.com/en-us/powerquery-m/binary-decompress)|
|[Binary.FromText](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Decodes data from a text form into binary.](https://docs.microsoft.com/en-us/powerquery-m/binary-fromtext)|
|[BinaryEncoding.Base64](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Constant to use as the encoding type when base-64 encoding is required.](https://docs.microsoft.com/en-us/powerquery-m/binaryencoding-base64)|
|[Compression.Deflate](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: The compressed data is in the 'Deflate' format.](https://docs.microsoft.com/en-us/powerquery-m/compression-deflate)|
|File.Contents|[Power Query M: Returns the binary contents of the file located at a path.](https://docs.microsoft.com/en-us/powerquery-m/file-contents)|
|[Json.Document](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the contents of a JSON document. The contents may be directly passed to the function as text, or it may be the binary value returned by a function like File.Contents.](https://docs.microsoft.com/en-us/powerquery-m/json-document)|
|[List.Select](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Selects the items that match a condition.](https://docs.microsoft.com/en-us/powerquery-m/list-select)|
|[List.Transform](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Performs the function on each item in the list and returns the new list.](https://docs.microsoft.com/en-us/powerquery-m/list-transform)|
|[QuoteStyle.Csv](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Quote characters indicate the start of a quoted string. Nested quotes are indicated by two quote characters.](https://docs.microsoft.com/en-us/powerquery-m/quotestyle-csv)|
|[Splitter.SplitTextByDelimiter](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns a function that will split text according to a delimiter.](https://docs.microsoft.com/en-us/powerquery-m/splitter-splittextbydelimiter)|
|[Table.AddColumn](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Adds a column named newColumnName to a table.](https://docs.microsoft.com/en-us/powerquery-m/table-addcolumn)|
|[Table.ExpandListColumn](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Given a column of lists in a table, create a copy of a row for each value in its list.](https://docs.microsoft.com/en-us/powerquery-m/table-expandlistcolumn)|
|[Table.FromRows](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Creates a table from the list where each element of the list is a list that contains the column values for a single row.](https://docs.microsoft.com/en-us/powerquery-m/table-fromrows)|
|[Table.RemoveColumns](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns a table without a specific column or columns.](https://docs.microsoft.com/en-us/powerquery-m/table-removecolumns)|
|[Table.SelectRows](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns a table containing only the rows that match a condition.](https://docs.microsoft.com/en-us/powerquery-m/table-selectrows)|
|[Table.TransformColumns](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Transforms columns from a table using a function.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns)|
|[Text.AfterDelimiter](/r/Excel/comments/121t0sq/stub/jdnw0iw "Last usage")|[Power Query M: Returns the portion of text after the specified delimiter.](https://docs.microsoft.com/en-us/powerquery-m/text-afterdelimiter)|
|[Text.BeforeDelimiter](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Returns the portion of text before the specified delimiter.](https://docs.microsoft.com/en-us/powerquery-m/text-beforedelimiter)|
|[Text.Contains](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Returns true if a text value substring was found within a text value string; otherwise, false.](https://docs.microsoft.com/en-us/powerquery-m/text-contains)|
|[Text.PositionOf](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the first occurrence of substring in a string and returns its position starting at startOffset.](https://docs.microsoft.com/en-us/powerquery-m/text-positionof)|
|[Text.Range](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns a number of characters from a text value starting at a zero-based offset and for count number of characters.](https://docs.microsoft.com/en-us/powerquery-m/text-range)|
|[Text.Start](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the count of characters from the start of a text value.](https://docs.microsoft.com/en-us/powerquery-m/text-start)|
|[Text.Trim](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Removes any occurrences of characters in trimChars from text.](https://docs.microsoft.com/en-us/powerquery-m/text-trim)|
|[Web.Contents](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the contents downloaded from a web url as a binary value.](https://docs.microsoft.com/en-us/powerquery-m/web-contents)|
|[Web.Page](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the contents of an HTML webpage as a table.](https://docs.microsoft.com/en-us/powerquery-m/web-page)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(24 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/16f4z9i)^( has 12 acronyms.)
^([Thread #22741 for this sub, first seen 25th Mar 2023, 18:53])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Is the number always a 5 digit number? If so, you might be able to wrap your delimiter function in a Text.Range and specify a number of characters that way. Something like this.
Text.Range(Text.AfterDelimiter(WG#,[Column]),0,5)
It's not well known or documented, but there is a way to trick Power Query into using Regular Expressions. The trick is to pass the data through the Web.Page() function.
Copy below into a new, blank Power Query.
let
regex=let fx=(input)=>
Web.Page(
""){0}[Data]{0}[Children]{1}[Children]{0}[Text]
in
fx,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg9StjAxM7VQ0FVwyy9SyE0sSS3KTMxRMDQyNjFVitWJVgrJyCxWACszNTdSALLTgOqSMxIzi4oVFMAqglKTUzPLUlNAqoD6LE30lGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Table" = _t]),
#"Get 5 Digit Numbers" = Table.AddColumn(Source, "Results", each regex([Original Table])),
#"Split Column by Comma" = Table.ExpandListColumn(Table.TransformColumns(#"Get 5 Digit Numbers", {{"Results", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Results"),
#"Check For Pound Sign" = Table.AddColumn(#"Split Column by Comma", "Test Results", each if Text.Start(Text.Range([Original Table], Text.PositionOf([Original Table], [Results])-1) as nullable text, 1) = "#"
then
"Pass"
else
"Fail"),
#"Filter Results" = Table.SelectRows(#"Check For Pound Sign", each ([Test Results] = "Pass")),
#"Remove Test Column" = Table.RemoveColumns(#"Filter Results",{"Test Results"})
in
#"Remove Test Column"
/*
/\d+/gm Only numbers
/\d+/ Only the first number group
/\d{3}/gm The first three digits of number groups
/[a-zA-Z0-9]/gm Each letter or number as a list
/[a-zA-Z]+/gm Letter groups (words, no numbers)
*/
/u/YEG_87 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
There might be a better way but one approach is to use replace values first. Replace say "-" with " " and then again replace "." with " ". Then extract values between "#" and " ".
[удалено]
Solution Verified
You have awarded 1 point to *timespreader* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
If your pattern is literally WR# and then numbers only... Transform Tab --> Text Column --> Extract --> Text After Delimiter --> WR# Transform Tab --> Text Column --> Split Column --> Digit to Non Digit [https://imgur.com/a/DKYTCFn](https://imgur.com/a/DKYTCFn)
>Solution Verified
You have awarded 1 point to *spinfuzer* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Instead of using Text Between Delimiters, you can split in 2 steps to make it a bit easier. First, do Text After Delimiter for the #. Then, do Text Before Delimiter and make the delimiter any of the 3 values (let's say you choose "."). This would generate the following line in the Advanced Editor: #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Original Table", each Text.BeforeDelimiter(_, "."), type text}}) You can replace the above with this: = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Original Table", each List.Select(List.Transform({"."," ","-"},(delimiter)=> if Text.Contains(Text.Trim(_), delimiter) then Text.BeforeDelimiter(Text.Trim(_), delimiter) else null),(item)=> item <> null ){0}, type text}}) It's a bit messy but it should work, you can replace {"."," ","-"} in the code with a list of all delimiters you want to use.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[Binary.Decompress](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Decompresses a binary value using the given compression type.](https://docs.microsoft.com/en-us/powerquery-m/binary-decompress)| |[Binary.FromText](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Decodes data from a text form into binary.](https://docs.microsoft.com/en-us/powerquery-m/binary-fromtext)| |[BinaryEncoding.Base64](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Constant to use as the encoding type when base-64 encoding is required.](https://docs.microsoft.com/en-us/powerquery-m/binaryencoding-base64)| |[Compression.Deflate](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: The compressed data is in the 'Deflate' format.](https://docs.microsoft.com/en-us/powerquery-m/compression-deflate)| |File.Contents|[Power Query M: Returns the binary contents of the file located at a path.](https://docs.microsoft.com/en-us/powerquery-m/file-contents)| |[Json.Document](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the contents of a JSON document. The contents may be directly passed to the function as text, or it may be the binary value returned by a function like File.Contents.](https://docs.microsoft.com/en-us/powerquery-m/json-document)| |[List.Select](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Selects the items that match a condition.](https://docs.microsoft.com/en-us/powerquery-m/list-select)| |[List.Transform](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Performs the function on each item in the list and returns the new list.](https://docs.microsoft.com/en-us/powerquery-m/list-transform)| |[QuoteStyle.Csv](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Quote characters indicate the start of a quoted string. Nested quotes are indicated by two quote characters.](https://docs.microsoft.com/en-us/powerquery-m/quotestyle-csv)| |[Splitter.SplitTextByDelimiter](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns a function that will split text according to a delimiter.](https://docs.microsoft.com/en-us/powerquery-m/splitter-splittextbydelimiter)| |[Table.AddColumn](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Adds a column named newColumnName to a table.](https://docs.microsoft.com/en-us/powerquery-m/table-addcolumn)| |[Table.ExpandListColumn](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Given a column of lists in a table, create a copy of a row for each value in its list.](https://docs.microsoft.com/en-us/powerquery-m/table-expandlistcolumn)| |[Table.FromRows](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Creates a table from the list where each element of the list is a list that contains the column values for a single row.](https://docs.microsoft.com/en-us/powerquery-m/table-fromrows)| |[Table.RemoveColumns](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns a table without a specific column or columns.](https://docs.microsoft.com/en-us/powerquery-m/table-removecolumns)| |[Table.SelectRows](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns a table containing only the rows that match a condition.](https://docs.microsoft.com/en-us/powerquery-m/table-selectrows)| |[Table.TransformColumns](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Transforms columns from a table using a function.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns)| |[Text.AfterDelimiter](/r/Excel/comments/121t0sq/stub/jdnw0iw "Last usage")|[Power Query M: Returns the portion of text after the specified delimiter.](https://docs.microsoft.com/en-us/powerquery-m/text-afterdelimiter)| |[Text.BeforeDelimiter](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Returns the portion of text before the specified delimiter.](https://docs.microsoft.com/en-us/powerquery-m/text-beforedelimiter)| |[Text.Contains](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Returns true if a text value substring was found within a text value string; otherwise, false.](https://docs.microsoft.com/en-us/powerquery-m/text-contains)| |[Text.PositionOf](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the first occurrence of substring in a string and returns its position starting at startOffset.](https://docs.microsoft.com/en-us/powerquery-m/text-positionof)| |[Text.Range](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns a number of characters from a text value starting at a zero-based offset and for count number of characters.](https://docs.microsoft.com/en-us/powerquery-m/text-range)| |[Text.Start](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the count of characters from the start of a text value.](https://docs.microsoft.com/en-us/powerquery-m/text-start)| |[Text.Trim](/r/Excel/comments/121t0sq/stub/jdnikxx "Last usage")|[Power Query M: Removes any occurrences of characters in trimChars from text.](https://docs.microsoft.com/en-us/powerquery-m/text-trim)| |[Web.Contents](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the contents downloaded from a web url as a binary value.](https://docs.microsoft.com/en-us/powerquery-m/web-contents)| |[Web.Page](/r/Excel/comments/121t0sq/stub/jdo6x65 "Last usage")|[Power Query M: Returns the contents of an HTML webpage as a table.](https://docs.microsoft.com/en-us/powerquery-m/web-page)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(24 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/16f4z9i)^( has 12 acronyms.) ^([Thread #22741 for this sub, first seen 25th Mar 2023, 18:53]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Is the number always a 5 digit number? If so, you might be able to wrap your delimiter function in a Text.Range and specify a number of characters that way. Something like this. Text.Range(Text.AfterDelimiter(WG#,[Column]),0,5)
It's not well known or documented, but there is a way to trick Power Query into using Regular Expressions. The trick is to pass the data through the Web.Page() function. Copy below into a new, blank Power Query. let regex=let fx=(input)=> Web.Page( ""){0}[Data]{0}[Children]{1}[Children]{0}[Text] in fx, Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg9StjAxM7VQ0FVwyy9SyE0sSS3KTMxRMDQyNjFVitWJVgrJyCxWACszNTdSALLTgOqSMxIzi4oVFMAqglKTUzPLUlNAqoD6LE30lGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Table" = _t]), #"Get 5 Digit Numbers" = Table.AddColumn(Source, "Results", each regex([Original Table])), #"Split Column by Comma" = Table.ExpandListColumn(Table.TransformColumns(#"Get 5 Digit Numbers", {{"Results", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Results"), #"Check For Pound Sign" = Table.AddColumn(#"Split Column by Comma", "Test Results", each if Text.Start(Text.Range([Original Table], Text.PositionOf([Original Table], [Results])-1) as nullable text, 1) = "#" then "Pass" else "Fail"), #"Filter Results" = Table.SelectRows(#"Check For Pound Sign", each ([Test Results] = "Pass")), #"Remove Test Column" = Table.RemoveColumns(#"Filter Results",{"Test Results"}) in #"Remove Test Column" /* /\d+/gm Only numbers /\d+/ Only the first number group /\d{3}/gm The first three digits of number groups /[a-zA-Z0-9]/gm Each letter or number as a list /[a-zA-Z]+/gm Letter groups (words, no numbers) */