Extracting Middle Section from String in Google Sheets

Other Questions Google Sheets
1

Hey everyone, I need some help with a Google Sheets problem. I’ve got a sheet that’s constantly getting new data from Zapier. One column has PayPal descriptors with important info I need to use.

Here’s what the data looks like:

Annual Fee-66421763-07142022191540

I want to grab just the middle part between the dashes. So from that example, I’d end up with:

66421763

The tricky part is that while the first part (“Annual Fee”) usually stays the same, the middle and end parts can change in length.

Does anyone know a good way to do this? Maybe using REGEX or some other formula? I’m not great with complicated spreadsheet stuff, so any tips would be super helpful. Thanks!

2

I’ve dealt with a similar situation in my work, and I found a reliable solution using the REGEXEXTRACT function in Google Sheets. Here’s what worked for me:

=REGEXEXTRACT(A1, “-(\d+)-”)

This formula assumes your PayPal descriptor is in cell A1. The regex pattern looks for numbers between two dashes. It’s been pretty robust in my experience, even when the lengths of the parts vary.

One thing to watch out for: make sure there aren’t any other numbers with dashes in your descriptors, as that could throw off the extraction. If you run into issues, you might need to tweak the regex pattern a bit.

Hope this helps! Let me know if you need any clarification on how to implement this in your sheet.

3

hey, have u tried the MID function? it’s pretty straightforward:

=MID(A1, FIND(“-”, A1) + 1, FIND(“-”, A1, FIND(“-”, A1) + 1) - FIND(“-”, A1) - 1)

this grabs everything between the first and second dash. works great for me, hope it helps!

4

I’ve encountered this issue before, and there’s actually a simpler solution using the SPLIT function combined with INDEX. Here’s the formula I’ve used successfully:

=INDEX(SPLIT(A1, “-”), 1, 2)

This splits the text at each dash and then selects the second element (index 2). It’s quite versatile and should work even if the lengths of the different parts vary.

One advantage of this method is that it doesn’t rely on the middle section being purely numeric, which might be beneficial if your data ever changes format. It’s also generally easier to understand and modify if needed.

Just replace A1 with your actual cell reference, and you should be good to go. Let me know if you have any questions about implementing this.