Searching in a cell Right to Left but stopping at the first instance

Posted on

QUESTION :

I have column A with “Customer Name-Account Number (Serial-number)”. So, an example could look like:

ABC-12345 (1s39-a29a-23sh-akjf).  

What I’m trying to get is just the customer name “ABC”.

The serial number is a constant character list so I’ve used:

=LEFT(A14, LEN(A14)-39) 

Which will return “ABC-12345”. But the customer numbers (12345) have slightly different character lengths.

So I have used the formula:

=LEFT(A14,FIND("-",A14)-1)

To give me everything left of the dash “-“.

However, some of the company names may also include a “-“. So, instead of “J-Systems-12345” I’m getting “J”.

Is there anyway to look right to left but stop looking at the first “-” that is found and return everything left of that “-“?

ANSWER :

Since your Serial number length is fixed we can use this formula which will find the last - before the serial number and use that to limit the LEFT() function:

 =LEFT(A1,FIND("}}}",SUBSTITUTE(A1,"-","}}}",LEN(LEFT(A1,LEN(A1)-22))-LEN(SUBSTITUTE(LEFT(A1,LEN(A1)-22),"-",""))))-1)

I had to change the -39 to -22 as the data provided the serial is only 22 characters. You may need to put it back to -39 for your real data.

enter image description here

Leave a Reply

Your email address will not be published.