Split text in google sheet on multiple conditions
I want to split the text on multiple conditions.
Here is sheet
this is the text that I want to split.
[{ name : Lot 230: Condition 1 Waterproof Protective Hard Case with Foam, Orange 9\ x 7\ x 4\ Watertight IP67 Dust Proof and Shock Proof TSA Approved Portable Carrier , item_id :8259332, bid_id :113235886, single_amount : 10.0 , amount : 10.0 , quantity :1, charges_amount : 1.3 , buyers_charge_id :361, tax_amount : 0.9 , tax_rate_id :161, total_amount : 12.2 , removed :null, removed_reason :null},{ name : Lot 253: Honeywell Home RTH9585WF1004 Wi-Fi Smart Color Thermostat, 7 Day Programmable, Touch Screen, Energy Star, Alexa Ready, Gray , item_id :8259353, bid_id :113236249, single_amount : 12.0 , amount : 12.0 , quantity :1, charges_amount : 1.56 , buyers_charge_id :361, tax_amount : 1.08 , tax_rate_id :161, total_amount : 14.64 , removed :null, removed_reason :null}]
Text can have information about many products. As you can see above text have information about 2 products. I just want the names of both products in separate columns.
Col1:
Lot 230: Condition 1 Waterproof Protective Hard Case with Foam, Orange 9\ x 7\ x 4\ Watertight IP67 Dust Proof and Shock Proof TSA Approved Portable Carrier
Col2:
Lot 253: Honeywell Home RTH9585WF1004 Wi-Fi Smart Color Thermostat, 7 Day Programmable, Touch Screen, Energy Star, Alexa Ready, Gray
try:
=ARRAYFORMULA(IFERROR(REGEXREPLACE(SPLIT(REGEXREPLACE(A3:A,
"^\[", ), "{ name : ", ), " , item_id.*$", )))
- A Regex to ensure that a string contains at least two words
- Regex: Match specific characters in any order without more occurrences of each character than specified
- bash + sed: trim trailing zeroes off a floating point number?
- Difference between ?:, ?! and ?=
- Perl search and replace words in file
- Regex to extract WordPress wp-config database information and automating it with ansible to update a shared hosting environment
- Combining regular expressions in Javascript
- Does the Swift Package Manager support regex literals?
- How to extract a substring from EDL line, between 2 sequence of characters
- RegexRenamer, how to add leading zeros?
- Understanding positive and negative lookaheads
- Regex - Does not contain '<' &' >'Characters
- How can I generate paging links from the URL when using mod_rewrite changes the URL
- Extract 12 character from a string impala query
- I am having an issue with replaceAll in MongoDB
- Using sed to delete all lines between two matching patterns
- Check for at least 1 digit and 1 letter within the first n characters
- Pattern attribute value is not a valid regular expression
- Get colon-prefixed placeholder names from a SQL string
- Extract values from a double-pipe- separated string also wrapped in pipes and square braces
- Multi-selection with regex (sublime text 2)
- Regex to match one of two words
- "The big SQL RegEx": How do I RegEx split an easy SQL query (SELECT ... INTO ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY)?
- Split string before and after double-braced placeholders
- Splitting paragraph into sentences keeping the punctuations - not a dup
- Split string by newlines
- Replace a series of the same character by its number of occurrences in the series
- How to validate 12-Hour Time with regex (regular Expression)
- How can I match a pattern as long as it's not at the beginning with regex?
- Lat/Long mask positive/negative detection