I was recently using an application which exported a report into Microsoft Excel format. The report separated most fields into separate cells, but for some reason, it combined certain fields together into once cell. Unfortunately, I needed a way to separate the combined data into two cells so I could use a filter. The combined data was “free form” with no field separating characters – i.e. a comma, apostrophe or quotation marks – so I could not use the “text to columns” function to break the data apart. The “text-to-column” function can easily break apart lists of data separated by a comma (or any other character), but is powerless if you want to separate data based on anything more than one character.
Fortunately, each value had a specific word that preceded the data I wanted to isolate. In my case, each value had the word “Document:” (including the semi-colon) before the text I wanted to isolate. The solution required a combination of native Microsoft Excel functions FIND and MID. See below for instructions for separating everything to the right of a particular word in a cell using a basic example of name and address.
For example, pretend you have the following text in a cell: “Name: John Smith Address: 123 Fake Street, Somewhere, XX 55555”
If you’re just dealing with one record, it’s easy enough to cut and paste into another cell. However, if you have a list of hundreds of records, you will want a more automated way to separate the addresses.
Back to the example… let’s assume we want to isolate everything after the text, “Address:” and the text is in cell A1. Highlight the adjacent cell, B1, and type the following:
Translated to English, this function is looking for the text “Address:” in cell A1, and it will start it’s search with character #1 in cell A1. If you changed the 1 to a 7, the search for “Address:” would begin with the “J” in “John” rather than the “N” in “Name”.
The result of the FIND function is the character at which the “Address:” text began (aka: “A”).
Back to the example… we determined the character at which our target word starts, we can isolate everything to the right of that word using the MID function in cell C1. This MID function is looking at the A1 cell (the original text), starting at the 27th character in that text (18 plus 9 characters), and returning the next 100 characters in the original text. We get 9 characters by counting the number of characters in the string “Address:”. The word address has 8 letters, and the semi-colon brings us to 9 total characters.
The 100 characters is arbitrary and can be adjusted to fit how much text you need to capture from your original text. I’m not sure how high you can make this number, but I make sure to change it to adjust to your data. Otherwise, if you have more than 100 characters in your own spreadsheet, it will cut everything off after the 100th character.
To finalize your data, you can simply hide “Column B”, or whichever column you’re using in your spreadsheet. I hope this tutorial has been helpful. I did a quick search for this topic on Google and only 1 tutorial seemed to be relevant (by relevant, I mean 3 years old), but there are a lot of useless tutorials out there which waste the readers time. Let me know if you have any helpful variations to this technique in the comments!
There are no comments yet. Why not be the first to speak your mind.