I have column A with “Customer Name-Account Number (Serial-number)”. So, an example could look like:
What I’m trying to get is just the customer name “ABC”.
The serial number is a constant character list so I’ve used:
Which will return “ABC-12345”. But the customer numbers (12345) have slightly different character lengths.
So I have used the formula:
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:
I had to change the
-22 as the data provided the serial is only 22 characters. You may need to put it back to
-39 for your real data.