Forum Discussion
Search, Isolate, and Transform Single Words in Phrase
Is it possible to isolate and transform a single word or phrase in excel power query instead of transforming the text in the entire cell?
For example, I have a column of text with 300 words per row. I want to search within the text for a word or phrase from a list of keyword that comes from a different table. Every occurrence where a keyword is found in the text I want to capitalize that keyword.
I can almost accomplish what I am explaining here but right now I am only able to capitalize the entire row of text when a keyword is found. How can I isolate a single word without changing the rest of the text in the row?
- Example Kayword to Search For: "summer"
- Example Text Passage: "It's a wonderful summer day today. Last summer didn't have this nice of weather."
Example Output: "It's a wonderful SUMMER day today. Last SUMMER didn't have this nice of weather."
4 Replies
You can split the text at the spaces with Text.Split to make a list and then apply a transform for just the "summer" elements and then recombine with spaces.
= Text.Combine(List.Transform(Text.Split([TextColumn], " "), each if _ = "summer" then Text.Upper(_) else _), " ")
Pat
ppm1 Thanks for the response.
Some of the keywords and phrases I'm searching for have spaces in them so imagine the keyword in the above example was "last summer" so I want to output "LAST SUMMER". How can I account for this if I don't want to split the text by each space?
In that case, see scenario 5 in this article - Replace Values in Power Query M (Ultimate Guide) - BI Gorilla
Pat
Is it possible to use "replace" to replace a keyword or phrase from my list with the capitalized version?
