Thursday, 27 October 2016

Splitting strings in MS Excel without regular expressions

Consider a field in Excel that contains a number followed by a '\' character, followed by 3 letters.

For example: 123456789\ABC

No let us say that you would like to get the part of the string that exists before or after the '\' character into another field.

You could write a VBA script that uses regular expressions, but this can more easily be achieved using a combination of the RIGHT, LEFT, LEN and FIND methods.


RIGHT(<cell>,<numChars>) : This method takes the contents of the cell specified by <cell> from a start point that is <numChars> from the far right hand side of the string and returns that e.g. =RIGHT(A1, 3) would return ABC, if our example string was in cell A1.

LEFT(<cell>,<numChars>) : Returns characters from the left hand side of our value that is contained in the cell specified by <cell>, the number of characters it returns from the left of our string is determined by the <numChars> value e.g. =LEFT(A1,9) would return 123456789, if our example string was in cell A1.

LEN(<cell>) : As you might have guessed, returns the length of a value in the cell specified by <cell> e.g. =LEN(A1) would return 13 (appropriate as this article was written near Halloween), if our example string was in cell A1.

FIND(<char>, <cell>) : Returns the index of the character specified by <char> in the cell specified by <cell> e.g. =FIND("\", A1) would return 10, if our example string was in cell A1 as the '\' character is the 10th character in the string.

As you can see in the above examples for the RIGHT and LEFT methods, we have already split our string, so why do we need the FIND?

As with all things, we need to limit our assumptions, it would be wrong for us to assume that the number of characters before and after our '\' delimiter would be of the same length every time, so we need to determine the position of this character for every value in our field.

Getting the characters to the left : We know that =FIND("\", A1) gives us 10, this is one more than the number of characters to the left of our delimiter, so to get the characters we want we need to subtract one, our formula would be as follows:

=LEFT(A1, FIND("\", A1)-1)

Getting the characters to the right : Our example string is 13 characters long, our '\' delimiter is the 10th character, you can see where I am going with this, 10-3 gives us the 3, and that's the number of characters we need to get from the right of our string, so the formula is:

=RIGHT(A1, LEN(A1)-FIND("\", A1))

And that's it, as simple as that. Thank you for reading, I hope this helps someone out there let me know if it has been useful in the comments & feel free to share.

No comments:

Post a Comment