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
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