Text String Help... Again!!

ConnorForm

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 AG-POWER, 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), ""))

Toufong Vang

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!

James Keuning

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

ConnorForm

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

Sam_Harwart

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 "AG-PRO" & "AG-POWER" instances since it starts the search for the next hyphen after where we'd expect to find it for those retailers.

Toufong Vang

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!

ConnorForm

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!!

Toufong Vang

@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)

ConnorForm

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

Toufong Vang

@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 )

ConnorForm

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)