Text String Help... Again!!
Hello All, feel like I am always asking these text string questions but they are so confusing!!! I have a list of retailers and I am trying to just extract the Name of the retailer between either the 1st and 3rd "" or the 1st and 2nd "". The problem is with how some of these are formatted like the first one and second row. For the first row I would want Adams Power Equipment but for the second row I would want the formula to produce AGPOWER, INC.
Currently using this formula right now but it is only working for the ones with 3 "" and not returning values for the ones with 2 ""
=IF(COUNTIF([Retailer Full]@row, "***") = 0, MID([Retailer Full]@row, FIND("", [Retailer Full]@row) + 1, FIND("", [Retailer Full]@row, FIND("", [Retailer Full]@row, FIND("", [Retailer Full]@row) + 1) + 1)  FIND("", [Retailer Full]@row)  1), IF(COUNTIF([Retailer Full]@row, "**") = 2, MID([Retailer Full]@row, FIND("", [Retailer Full]@row) + 1, FIND("", [Retailer Full]@row, FIND("", [Retailer Full]@row) + 1)  FIND("", [Retailer Full]@row)  1), ""))
Best Answer

Hi, @ConnorForm , the following formula will work without helper columns.
=MID([Retailer Full]@row, FIND(" ", [Retailer Full]@row) + 2, FIND(" ", [Retailer Full]@row, FIND(" ", [Retailer Full]@row) + 2)  FIND(" ", [Retailer Full]@row) 2)
Here’s how it works…
Looking at the pattern, the retailer’s name is between two " –" (space and dash) and not ““ (single dash).
FIND(“ –“, [Retailer Full]@row)
returns the location of the first instance of " _". The retailer’s name begins 2 characters to the right of where the first “ –“ is found.First_Instance =
FIND(" –", [Retailer Full]@row)
Name starts at
FIND(" –", [Retailer Full]@row) + 2
To find the second instance of “ –“ (where the retailer’s name ends), your search must start some place to the right of where the first instance of it was found. In other words…
FIND(" –", [Retailer Full]@row, First_Instance + 2)
which is…
Second_Instance =
FIND(" –", [Retailer Full]@row, FIND(" –", [Retailer Full]@row) + 2)
The retailer’s name is between First_Intance and Second_Instance or,
Second_Instance – First_Instance
But the name does not include the first " –" so we need to subtract 2 to return its length…
Second_Instance – First_Instance – 2
Use MID() to extract the name…
MID([Retailer Full]@row, First_Instance + 2, Second_Instance – First_Instance – 2)
Substitute…
MID([Retailer Full]@row, FIND(" ", [Retailer Full]@row) + 2, FIND(" ", [Retailer Full]@row, FIND(" ", [Retailer Full]@row) + 2)  FIND(" ", [Retailer Full]@row)  2)
Cheers!
Answers

Have you put your COUNTIF([Retailer Full]@row, "**") in its own column to make sure that it is counting correctly?

Just checked, it is showing everything as 0 so it is not working correctly @James Keuning

Hi @ConnorForm ,
Can you add some helper columns? When you're doing these type of complex formulas, I find that helper columns to break up the text can be helpful in troubleshooting.
I would probably use a combination of Left/Right/Find/Len instead of the counting sorting, if the list of Retailers is relatively static or predictable.
=LEFT(RIGHT([Retailer Full]@row, LEN([Retailer Full]@row)  FIND("", [Retailer Full]@row)), FIND("", RIGHT([Retailer Full]@row, LEN([Retailer Full]@row)  FIND("", [Retailer Full]@row)), 5)  2)
This formula parses out the beginning numbers and the text after the final hyphen. The 5 starting position for the final Find formula gets us around the "AGPRO" & "AGPOWER" instances since it starts the search for the next hyphen after where we'd expect to find it for those retailers.
Cheers,
Sam
primeconsulting.com  Smartsheet's 2023 Partner of the Year for North America
Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call! 
Hi, @ConnorForm , the following formula will work without helper columns.
=MID([Retailer Full]@row, FIND(" ", [Retailer Full]@row) + 2, FIND(" ", [Retailer Full]@row, FIND(" ", [Retailer Full]@row) + 2)  FIND(" ", [Retailer Full]@row) 2)
Here’s how it works…
Looking at the pattern, the retailer’s name is between two " –" (space and dash) and not ““ (single dash).
FIND(“ –“, [Retailer Full]@row)
returns the location of the first instance of " _". The retailer’s name begins 2 characters to the right of where the first “ –“ is found.First_Instance =
FIND(" –", [Retailer Full]@row)
Name starts at
FIND(" –", [Retailer Full]@row) + 2
To find the second instance of “ –“ (where the retailer’s name ends), your search must start some place to the right of where the first instance of it was found. In other words…
FIND(" –", [Retailer Full]@row, First_Instance + 2)
which is…
Second_Instance =
FIND(" –", [Retailer Full]@row, FIND(" –", [Retailer Full]@row) + 2)
The retailer’s name is between First_Intance and Second_Instance or,
Second_Instance – First_Instance
But the name does not include the first " –" so we need to subtract 2 to return its length…
Second_Instance – First_Instance – 2
Use MID() to extract the name…
MID([Retailer Full]@row, First_Instance + 2, Second_Instance – First_Instance – 2)
Substitute…
MID([Retailer Full]@row, FIND(" ", [Retailer Full]@row) + 2, FIND(" ", [Retailer Full]@row, FIND(" ", [Retailer Full]@row) + 2)  FIND(" ", [Retailer Full]@row)  2)
Cheers!

Thank you so much @Toufong Vang sorry last question, hate to be a bother, but to get the last string to the right of the third or second "" so to pull "A&T" and "C&F" and a few are like "C&F Carnesville" & "A&T Ohio". I figured there would not be too many changes to the formula that worked but for some reason I am not getting it. Thanks again for the time and effort put into assisting me!!

@ConnorForm , to find the 3rd instance of " ", your FIND() will have to start 2 characters to the right of the Second_Instance of it. So it'll look like…
Third_Instance = FIND(" ", [Retailer Full]@row, Second_Instance + 2)
MID([Retailer Full]@row, Third_Instance + 2, 999)
However, if the pattern is consistently
<one_character>
+
"&"
+
<something>
then you can FIND() the character "&". Then start at 1 character to the left of it, and grab everything from that point to the end of the string—e.g., "999".FIND("&", [Retailer Full]@row)  1
MID([Retailer Full]@row, FIND("&", [Retailer Full]@row)  1, 999)

Thanks! Formula is working for the most part. However, I did not realize that there are some cases in the Retailer Full Column where there is a second "&". Apologies for these naming conventions and the lack of structure, I inherited this workspace and process so trying to clean it all up within my SS currently

@ConnorForm , in that case, I would use MID() to grab everything that follows the Second_Instance of " ".
MID( [Retailer Full]@row , Second_Instance + 2 , 999 ) 
so like this? @Toufong Vang
Second instance = FIND(" –", [Retailer Full]@row, FIND(" –", [Retailer Full]@row) + 2)
=MID([Retailer Full]@row, FIND(" –", [Retailer Full]@row, FIND(" –", [Retailer Full]@row) + 2) + 2, 999)
Help Article Resources
Categories
Check out the Formula Handbook template!