Extracting specific data from text in Google Sheets: Is there a formula for this?

Other Questions Google Sheets
1

I’m working on a project in Google Sheets and need help with a formula. Here’s my problem:

I’ve got cells with text like this:

+12% ranged defense for cavalry units (global effect)

I want to make functions that can pull out certain parts:

  • Get the buff type (ranged defense)
  • Get the value (+12%)
  • Get the target (cavalry units)
  • Get the scope (global)

Is there a way to do this without manually going through each cell? I’m not great with formulas, so any tips would be super helpful!

I figure there must be a smart way to split up the text and grab what I need. Maybe using REGEXEXTRACT or something? Really hoping someone can point me in the right direction here. Thanks!

2

As someone who’s tackled similar data extraction challenges, I can share that REGEXEXTRACT is indeed your best bet here. But let me offer a slightly different approach that might make your life easier.

Instead of using separate formulas for each element, you could create a single custom function in Google Apps Script. This function would take the cell content as input and return an array with all four elements you need.

Here’s a rough idea of how it might look:

function extractBuffData(text) {
var value = text.match(/([±]\d+%)/)[1];
var buffType = text.match(/(\w+\s\w+) for/)[1];
var target = text.match(/for (\w+\s\w+)/)[1];
var scope = text.match(/((\w+)\s*effect)/)[1];

return [value, buffType, target, scope];
}

You’d then use this in your sheet with =extractBuffData(A1), and it would populate four cells with the extracted data. This approach is more maintainable and easier to tweak if your data format changes in the future.

3

hey there SoaringEagle! regex is def the way to go here. u can use REGEXEXTRACT with patterns like these:

buff: “(\w+\s\w+)”
value: “([±]\d+%)”
target: “for (\w+\s\w+)”
scope: “((\w+)\s*effect)”

hope this helps u out! lemme kno if u need more help :slight_smile:

4

REGEXEXTRACT is indeed the most efficient solution for your task. Here’s a breakdown of the formulas you can use:

=REGEXEXTRACT(A1, “([±]\d+%)”) for the value
=REGEXEXTRACT(A1, “(\w+\s\w+) for”) for the buff type
=REGEXEXTRACT(A1, “for (\w+\s\w+)”) for the target
=REGEXEXTRACT(A1, “((\w+)\s*effect)”) for the scope

Replace A1 with the cell containing your text. These formulas should extract the specific data you need. Remember to adjust the patterns if your text format varies. Test them on a few examples to ensure accuracy.