Identifying part of a string with multiple cases

Hi everyone,

I've got multiple strings that I wish to extract various parts of the string out.

There are multiple cases that need to be considered when doing this as there are no 2 strings from my list of strings that are the same, hence why i need to account for the different possible cases

some examples of Strings:

MyString = "Project1234 -MS- SUBURB street name and details"
or
MyString = "Project1234 -MS- SUBURB NAME street name and details"
or
MyString = "Project1234 -MS- SUBURB id123 details"
or
MyString = "Project1234 -MS- SUBURB ID123 details"
or
MyString = "Project1234 -MS- SUBURB ID123 details"

The information i need:
(from the example above)
Project_ID = "Project1234"
Employee_Initials = "MS"
Suburb = "SUBURB" Or (in the case of a 2 word string for suburb) "SUBURB NAME"

Identifying the suburb is where I need your help.
The suburb always starts after the second dash (-) in MyString.

The suburb always finishes at the first instance of any of the following:
When a string with a number appears (i.e. id123)
When a word that isnt entirely capitals appears (i.e street name/ Street name)
When any type of special character appears (i.e. ~ , . > < # @ $ % ^ & etc)

If you could please provide some assistance that would be greatly appreciated!

Jordan

To Attach a Workbook:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit

All in one go.

Please Login or Register  to view this content.

thank you for your reply jindon and nilem.

Jindon,
The code works perfectly and for the most part completely solves the problem i was having.

How could I modify it slightly as to ignore the spaces between dashes in the strings?
(i.e. for some strings there will be a space either side of a dash, others may have one space on one side, others may have no spaces at all, so to ignore any spaces all together would improve the resolution of this function)

If someone could please explain or point me into the right direction in regards to understanding the .Pattern function that would be greatly appreciated!

Regards
Jordan

Please Login or Register  to view this content.

^ represents beginning of string
([^\-]+)
parentheses makes group of characters
square brackets represents inclusive any characters inside.
when "^" used　inside, it is opposite (exception)
\- represents hyphen, it needs to be escaped by forward slush as - is a metacharacter.
+ represents one or more.

so, ^([^\-]+) means group of character(s) that exclude hyphen from the beginning.

?
a space means a space itself and ? represents one or none.
So it means with/without space

([A-Z]+( ([A-Z]+)(?!\d))*)\b.*
This one is a bit tricky and the key of this pattern.
[A-Z]+ means any character(s) between A to Z
( ([A-Z]+)(?!\d))*
* represents none or many
when ? used with open round brackets, it excludes from backward reference. (talk about this later)
and when ?! used with open round brackets, it is a negative match, means the characters that doesn't follow the character(s) specified.
\d represents number.
So it means one or more character(s) of A to Z and none or may of the string that have a space with A to Z that doesn't have numbers followed.

.*
Period represents any character, so it is simply none or many of any characters.

Backward reference
You can retrieve any submatches within a pattern via index of open round brackets EXCEPT those with "?".

So, ^([^\-]+) will be $1, dollar sign should be used with Replace method and the index starts from 1 and it only can refer up to 9.
But when referencing Submatches collection from Execute method, index starts from 0.
(it is not used in your case, so just for the information)

Google the net and you will find many good sites explaining this, though only a few can be found for the information regarding negative match....

Good luck.

Thanks again jindon. Your explanation is great and I've now got a good understanding of how to use these pattern operators!

Also How would I modify this so that I can use it as a function that returns the 3 values?

Cheers