Help with combining LEFT and FIND function with multiple stipulations.

[tommybone30]

I need to find the best solution for this Excel problem.

I've done a formula where a cell will populate anything to the left of the first hyphen in the adjacent cell. This is essentially a 2500 cells project, but for simplicity sake I'll only use about 5 cells out of 2500.

For example:

Cell A1:
Sales 3220-General-Remote-COI-Support
Cell A2:
Sales & Marketing-General-Woodland Hills-ACT-Admin

With the formula "=LEFT(A1,FIND("-",A1)-1)" and "=LEFT(A2,FIND("-",A2)-1)" in cells B1 and B2, this will populate:

Cell B1:
Sales 3220
Cell B2:
Sale & Marketing

In this Excel sheet, 99.6% of the cells used the above formula, which works great. However, there are a few cells where I need to include data in between the first and second hyphen, instead of before the first hyphen. For example:

Cell A3:
Sales-1st Party 3221-General-Atlanta-COI-Support
Cell A4:
Sales-3rd Party 3223-General-Atlanta-COI-Support
Cell A5:
Sales-3rd Party 3223-General-Remote-COI-Support

I'm looking for a formula where if these cells do not contain any texts in cells A3, A4, and A5, it would display any cell data similar to B1 and B2, but if it does contain cells A3, A4, A5, it would display the following:


Cell B3:
Sales-1st Party 3221
Cell B4:
Sales-3rd Party 3223
Cell B5:
Sales-3rd Party 3223

Please help me out! Let me know if this question isn't clear.

 

[jtakw]

Hi,

Would this work for you?

Excel 2010
	A	B
1	Sales 3220-General-Remote-COI-Support	Sales 3220
2	Sales & Marketing-General-Woodland Hills-ACT-Admin	Sales & Marketing
3	Sales-1st Party 3221-General-Atlanta-COI-Support	Sales-1st Party 3221
4	Sales-3rd Party 3223-General-Atlanta-COI-Support	Sales-3rd Party 3223
5	Sales-3rd Party 3223-General-Remote-COI-Support	Sales-3rd Party 3223
Sheet2

Cell Formulas
Range	Formula
B1	=IF(MID(A1,6,1)="-",LEFT(A1,FIND("-",A1,7)-1),LEFT(A1,FIND("-",A1)-1))

B1 formula copied down.

 

[tommybone30]

jtakw

In this specific case, it does work. However, there are more texts in the other cells that I have not mentioned that will pick up on your formula that I don't want it to use. In this case, I'm thinking more along the line as if there are texts containing "Sales-1st Party 3221-General-Atlanta-COI-Support", "Sales-3rd Party 3223-General-Atlanta-COI-Support", "Sales-3rd Party 3223-General-Remote-COI-Support", then they will be considered "Sales-1st Party 3221" and "Sales-3rd Party 3223"; otherwise just populate the text before the first hyphen.

 

[jtakw]

I'm not understanding, that's what my formula does.

 

[tommybone30]

jtakw

The examples I just gave you were only 5 cells out of 2500. While the code may be the one that'll work for Sales-1st Party and Sales-3rd Party (which are the only cells that needed to show the text between the first and second hyphen, if I copy and paste the formula to all 2500 cells, there will be other cells that will display the text between the first and second hyphens that I do not want to have. For example, here are several text data that will inadvertently get the texts between the first and second hyphens based on the your formula.

Original: Legal-General-Woodland Hills-ACT-Admin
With Your Code: Legal-General
What I Want: Legal

Original: Admin-General-Bakersfield-ACT-ED
With Your Code: Admin-General
What I Want: Admin

The whole point of this complex coding is that if I apply one computation formula to all 2500, I want all the adjacent cells to show the text before the first hyphen WITH THE EXCEPTIONS of those with the Sales-1st Party and Sales-3rd Party, in which case I would need the data from in between the first and second hyphens as well as before the first hyphen.

Does that make a little more sense?

 

[jtakw]

Hi,

Sorry for not getting back sooner, been real busy last couple of days.

Seems like you're saying the ONLY cells that you want text shown between 1st and 2nd hyphens ALWAYS start with "Sales-", the following would work:

Excel 2010
	A	B
1	Sales 3220-General-Remote-COI-Support	Sales 3220
2	Sales & Marketing-General-Woodland Hills-ACT-Admin	Sales & Marketing
3	Sales-1st Party 3221-General-Atlanta-COI-Support	Sales-1st Party 3221
4	Sales-3rd Party 3223-General-Atlanta-COI-Support	Sales-3rd Party 3223
5	Sales-3rd Party 3223-General-Remote-COI-Support	Sales-3rd Party 3223
6	Legal-General-Woodland Hills-ACT-Admin	Legal
7	Admin-General-Bakersfield-ACT-ED	Admin
Sheet1

Cell Formulas
Range	Formula
B1	=IF(LEFT(A1,6)="Sales-",LEFT(A1,FIND("-",A1,7)-1),LEFT(A1,FIND("-",A1)-1))

B1 formula copied down.