«

»

Excel: select subset of string based on delimiter

Okay, imagine the following scenario: you are working in Excel and you have a string in column A that all look like this:

fieldA_fieldB_fieldC_fieldD_fieldE

You can pull them apart using the text-to-columns functionality that’s in Excel, but what if that option is not available? (For instance, it needs to be done right after you copy and paste in a new list).

Use the following tip to pull apart that string using nothing but excel text manipulation! In this example I will show you how to remove field D and field E from your string, but also show you how to adjust it for more flexibility!

I pull apart all the steps and will then show you the end result:

How to use it (using helper columns)

  1. In column B add “=SUBSTITUTE(A1;”_”;”#”;3)
  2. In column C add “=FIND(“#”;B1)”
  3. In column D add “=LEFT(A1;C1-1)

Explanation
 In step (1) you replace the 3rd occurrence of the underscore with a character that is not in the original string (adjust as needed).
In step  (2) you find this new character and return the result.
In step (3) you select only the part left of the result of your FIND command, meaning the string from will look like this after your manipulation:
fieldA_fieldB_fieldC

If you need a different selection, adjust the 3 in step (1) to the specific delimiter you want to search for.

Putting it all together
Using three helper columns is not very elegant and therefore you  can condense it all into one function:

=LEFT(A1;FIND(“#”; SUBSTITUTE(A1;”_”; “#”;3))-1)

Conclusion
The above tip gives you an easy way to manipulate string and pull apart cell data based on a certain delimiter. This can be very useful if you need to do grouping on higher level aspects of certain strings.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>