# Formula or VB to extract part of a string of varying formats

#### RusselJ

##### Board Regular
Hi All

I am trying to extract part of a string with a formula (or VB).

The string follows a standardish format and an example would be that cell A1 contains the following:

ABC-XYZ.123-9876

I am trying to extract everything after the first hyphen, so In B1 it would show:

XYZ.123-9876

The problems arise with the variations of the format:

The user may use a full stop in place of the first hyphen like so:
ABC.XYZ.123-9876

The user may enter all full stops and no hypen:
ABC.XYZ.123.9876

The first 3 letters could be between 3 and 4 letters long:
ABC.XYZ.123-9876
ABCD.XYZ.123-9876

Similarly, the last four digits could be between 1 and 6 digits long:
ABC.XYZ.123-987654

Or the biggest problem of all, the user may not enter the initial 3/4 letters (ABC/ABCD) meaning it is just showing the result we are looking for:
XYZ.123-9876

The only thing that remains constant in the is the three letters which are XYZ in this example (although they could be any 3 letters). They will always only be 3 letters in length.

Is there a formula or vb that can assist to extract the correct part of the string regardless of which of the above formats are used?

I would really appreciate your help!

Russel

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try this:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-4,40)
Excel Workbook
AB
1ABC-XYZ.123-9876XYZ.123-9876
2ABC.XYZ.123-9876XYZ.123-9876
3ABC.XYZ.123.9876XYZ.123.9876
4ABC.XYZ.123-9876XYZ.123-9876
5ABCD.XYZ.123-9876XYZ.123-9876
6ABC.XYZ.123-987654XYZ.123-987654
7XYZ.123-9876XYZ.123-9876
Sheet

That works excellent! Can you explain how it works?

Russel

Thanks for the feedback.

The Find function finds the position of the digits, the Min function finds the position of the first digit in the string. In the samples provided the substring always contains 3 letters and a dot before the first digit, so the strings to extract start at 4 positions left to the first number.

Digits between quotes are joined to the string to avoid getting #Value error when the Find function does not find any digit between 0-9 in the original string.
The length of the string is set to 40 for possible longer strings.

Replies
1
Views
1K
Replies
2
Views
1K
Replies
1
Views
477
Replies
19
Views
1K
Replies
9
Views
830

1,217,347
Messages
6,136,045
Members
449,982
Latest member
josephinelinnea

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back