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

RusselJ

Board Regular
Joined
Aug 5, 2013
Messages
155
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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
 

RusselJ

Board Regular
Joined
Aug 5, 2013
Messages
155
That works excellent! Can you explain how it works?

Thanks for your help!

Russel
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,822
Members
416,342
Latest member
BlueDevil12

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top