# 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 “-“?

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. 