Text String Question

ConnorForm

Hello All,

Quick question I just need this 4-6 digit number before - Retailer in this column but cannot get my formula to work since what I am pulling ranges from 4-6 digits.

For example In my new column trying to create, I would just need 59552 from59552 -Martin Tractor or 178874 from 178874 - BROOKS TRACTOR INCORPORATED

KPH

Hi @ConnorForm

If all your cells have a space after the 4-6 digit number then you are in luck. You can determine the length of the text to extract based on the position of the space.

=LEFT(Retailer@row, FIND(" ", Retailer@row) - 1)

The FIND will return the position of the space. Subtracting 1 will give the number of characters before the space. Using this as the number of characters part of the LEFT function will return just the characters to the left of the space.

Like this:

ConnorForm

This is perfect. Thank you so much @KPH

KPH

Hi @ConnorForm

If all your cells have a space after the 4-6 digit number then you are in luck. You can determine the length of the text to extract based on the position of the space.

=LEFT(Retailer@row, FIND(" ", Retailer@row) - 1)

The FIND will return the position of the space. Subtracting 1 will give the number of characters before the space. Using this as the number of characters part of the LEFT function will return just the characters to the left of the space.

Like this:

ConnorForm

@KPH Hello and thanks again for answering my question!!! Real quick follow up, how would I adjust the formula to capture the retailer name in between the two "-"? For example in 87244 - Grossenburg Implement, INC - AT&T, I would just want the formula to pull "Grossenburg Implement, INC"

KPH

Hi @ConnorForm

Good to hear that answer worked for you. To find the text between the - you can use a MID function (very similar to how we used LEFT last time). You need to tell the function where to start and how many characters to extract which you can do using FIND again. We will also use SUBSTITUTE to find the second hyphen. This will only work if the name is between two hyphens. If there are hyphens within the name we will have a problem.

Step 1. Find Start Position

First you need to identify the starting position of the name, which we are assuming is always after the first hyphen.

=FIND("-", Retailer@row)

(just like how you found the space for the earlier formula)

Then add one to find the position of the first character after the hyphen:

=FIND("-", Retailer@row)+1

Step 2. Find End Position

Then you need to find the second hyphen in the string. You can't find the second occurrence of something but you can substitute the second occurrence of something. So we use a SUBSTITUTE function to convert the hyphen into a unique character (I am using *, if you have * in your data use something else).

=SUBSTITUTE(Retailer@row, "-", "*", 2)

Then we add a FIND function to find the position of that *:

=FIND("*", SUBSTITUTE(Retailer@row, "-", "*", 2))

Step 3. Calculate Length

Then we subtract the start position (position of the first hyphen) and the hyphen itself (that's the -1), from the end position (position of the second hyphen) to find the length of the company name:

=FIND("*", SUBSTITUTE(Retailer@row, "-", "*", 2)) - FIND("-", Retailer@row) - 1

Step 4. Extract Text for Middle

Now we know where the name starts and how long it is, we can use a MID function to return it.

The syntax is

=MID(text, start position, number of characters)

In other words

=MID(Retailer@row, formula 1, formula 3)

Or

=MID(Retailer@row, FIND("-", Retailer@row) + 1, FIND("*", SUBSTITUTE(Retailer@row, "-", "*", 2)) - FIND("-", Retailer@row) - 1)

This will include any spaces at the very start or end as they seem to be inconsistent. We can add another step to remove these if you need to.

ConnorForm

This is perfect. Thank you so much @KPH