A friend of mine for work needed a way to parse out a domain name from a user’s email address in Excel.  Normally I would have used Regular Expressions to do this but I couldn’t easily find them so I did the old school string find / substring route.  Expensive but effective.

So if I have lets say my Microsoft email address of crutkas@microsoft.com in the excel spreadsheet at row A1, I can do some neat stuff like the SEARCH and MID commands.

Lets define exactly what this nifty commands do.

• MID(text, start_index, num_characters): returns the characters from the middle of a text string, given a starting position and length
• SEARCH(find_text, within_text, start_index):  returns the index of the character for the first instance of the desired text reading from left to right.  This is not a case-sensitive search.

So to get “crutkas” returned, we’ll do this:

=MID(A1, 1, SEARCH("@", A1) - 1)

Excel’s index starts at 1 along with we need to subtract 1 to remove the @ sign.  Now lets go for the microsoft.com result.

This command is a bit longer since we need to start off at the @ symbol and move back.

=MID(A1, SEARCH("@", A1)  + 1, LEN(A1) - SEARCH("@", A1) )

So we get the length of the string and subtract where we find the @ symbol.

Simple, right.