Text String Question
Hello All,
Quick question I just need this 46 digit number before  Retailer in this column but cannot get my formula to work since what I am pulling ranges from 46 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
Best Answers

Hi @ConnorForm
If all your cells have a space after the 46 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:

This is perfect. Thank you so much @KPH
Answers

Hi @ConnorForm
If all your cells have a space after the 46 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:

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

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.

This is perfect. Thank you so much @KPH
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!