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

[RusselJ]

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!

Thanks for reading,

Russel

 

[István Hirsch]

Try this:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-4,40)

Excel Workbook
	A	B
1	ABC-XYZ.123-9876	XYZ.123-9876
2	ABC.XYZ.123-9876	XYZ.123-9876
3	ABC.XYZ.123.9876	XYZ.123.9876
4	ABC.XYZ.123-9876	XYZ.123-9876
5	ABCD.XYZ.123-9876	XYZ.123-9876
6	ABC.XYZ.123-987654	XYZ.123-987654
7	XYZ.123-9876	XYZ.123-9876
Sheet

 

[RusselJ]

That works excellent! Can you explain how it works?

Thanks for your help!

Russel

 

[István Hirsch]

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.