A delimiter with non-delimiter uses during data import in Excel
I ran into an interesting situation the other day when trying to import some data into Excel. I had a text file with words and definitions that I needed to have in two columns in Excel, one for the word and one for the definition.
The words and their definitions were separated by a hyphen (-), so I could have gone ahead and done a direct import and specified the hyphen as the delimiter. The tricky part was that the hyphen appeared in some of the definitions too, and so using it as a delimiter would have split some of the definitions into separate columns, and I would have had to go through the sheet and reunite the definitions. Given that I had over 10,000 entries, this was a less than optimal solution, and not very time efficient.
I work a lot with Excel and Excel formulas, but I am by no means an Excel guru, and I am sure there’s a pretty simple solution, maybe a macro or something, for this kind of situation. But I used a formula to solve my problem as explained below:
What I did was paste the contents of my text file into one column with no separation between word and definition, with each entry in it’s own row. I then wanted to split the column into two, one with the word and one with the definition. I then used the right, left, len, and find functions. So with my data in column A, I put the following formulas columns B and C respectively and filled down.
This worked perfectly, and solved my problem in seconds. Do you have any suggestions for a different way to do this?