I’m trying to get the first part of a string before an underscore in Google Sheets. My current formula works for strings with one underscore, but not for multiple underscores.
For example:
apple_banana returns apple (which is correct)cherry_grape_kiwi returns cherry_grape (but I want just cherry)
Here’s my current formula:
=REGEXEXTRACT(A1, "(.*)_")
Can anyone help me modify this formula to always get the text before the first underscore, even if there are more underscores in the string? I’m not great with regex, so a simple explanation would be really helpful. Thanks!
I’ve encountered this issue before and found a solution that works well. Instead of using REGEXEXTRACT, you can leverage the SPLIT function combined with INDEX. Here’s the formula:
=INDEX(SPLIT(A1, “_”), 1, 1)
This approach splits the text at each underscore and then returns the first element. It’s efficient and handles multiple underscores without any issues. I’ve used this method in several projects, and it’s been quite reliable. Just make sure your cell references are correct when implementing it in your sheet.
I’ve dealt with similar string manipulation challenges in my work. Here’s a robust solution using REGEXEXTRACT that should handle your case:
=REGEXEXTRACT(A1, “+”)
This regex pattern + does the following:
^ anchors the match to the start of the string
[^_]+ matches one or more characters that are not underscores
So it effectively captures everything from the start until it hits the first underscore.
I’ve found this approach particularly useful when dealing with inconsistent data formats. It’s also quite flexible if your requirements change later on. Give it a try and see if it solves your problem.
hey stella, try this formula:
=LEFT(A1, FIND(“_”, A1)-1)
it finds the first underscore and grabs everything to the left of it. should work for multiple underscores too. lemme know if it helps!