•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to remove all characters that are not between square brackets?
Our take
If you're looking to streamline your data in Excel by isolating text within square brackets, you're in the right place. This process is especially useful for tables with extensive rows and multiple bracketed texts per line. By leveraging Power Query, you can effectively remove all characters that fall outside of these brackets. The provided solution outlines a straightforward method to achieve this, ensuring that you retain only the desired content while enhancing your overall productivity.
Sorry if this is a repost but all the posts I was finding on how to achieve what I'm looking for offered the inverse of what I was after.
- There is a table with hundreds of thousands of rows.
- Two columns contain text.
- Within that text there are characters between square brackets, sometimes more than more than one square bracketed text per line.
How does one go about removing all characters that are not between square brackets (thus keeping all the text between the square brackets only)?
(Please be gentle, taking excel beyond just tables is relatively new to me.)
UPDATE: there are more than one square brackets in a line of text & I am using either Exel2016 or 2019 desktop versions.
SOLUTION UPDATE:
- Enter Power Query > Add Column > Custom Column
- Remember to change [YourColumnName] to the Column you wish to extract the Square Brackets and their Bracketed text From
Text.Combine( List.Transform( Text.Split([YourColumnName], "["), each if Text.Contains(_, "]") then "[" & Text.BeforeDelimiter(_, "]") & "]" else "" ) ) [link] [comments]
Read on the original site
Open the publisher's page for the full experience