0

In Google Sheets, I'm trying to return numbers from within a text string.

The numbers are values of the property named either item_id or item_ids.

I've tried the REGEXTRACT function but I'm struggling.

Example 1

  1.   Text in cell A1
    <!-- wp:shortcode -->
    [aces-unit-1 item_id=""1236""]
    <!-- /wp:shortcode -->
    
  2.   Expected Result
    1236
    

Example 2

  1.   Text in cell A1
    <!-- wp:shortcode -->
    [aces-bonuses-1 items_number=""4"" external_link=""1"" category="""" items_id=""1563, 2372, 3855, 3980"" exclude_id="""" parent_id="""" columns=""4"" order="""" orderby="""" title=""""]
    <!-- /wp:shortcode -->
    
  2.   Expected Result
    1563, 2372, 3855, 3980
    
2
  • What spreadsheet website are you using?
    – Laurel
    Nov 21 at 12:54
  • if the item_id vs item_ids is a typo and the string is always one or the other, edit your question to fix that and I can update the solution.
    – Blindspots
    Nov 21 at 17:22

1 Answer 1

0

Formula

=REGEXEXTRACT(A1, "items?_id=""""((?:\d{4}[,\s]{0,2})+)""""")

Explanation

  1. Matches (non-capture) (?:) 4-digit number groups \d{4} followed by 0-2 commas and/or spaces [,\s]{0,2}
  2. Captures one or more of the matched number groups if immediately preceded by items?_id="""" and immediately followed by """""
    • the letter s is optional (item vs. items) s?
    • The consecutive (double) quotes "" are each escaped with an additional quote """"

#N/A Errors

If there is no match found, the formula returns a #N/A error. This can be handled using the IFNA function to return a blank of whatever value is preferred.

# IFNA returns nothing/blank 
=IFNA(REGEXEXTRACT(A1, "items?_id=""""((?:\d{4}[,\s]{0,2})+)"""""))

# IFNA returns something
=IFNA(REGEXEXTRACT(A1, "items?_id=""""((?:\d{4}[,\s]{0,2})+)"""""),<something>)

// Replace <something> with a string, number, cell reference, formula, etc. 

ARRAYFORMULAIFNA

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.