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.
=LEFT(A1,FIND(“-“,A1)-1)
=RIGHT(A1,LEN(A1)-FIND(“-“,A1))
This worked perfectly, and solved my problem in seconds. Do you have any suggestions for a different way to do this?
While there are usually 20 different ways to do something in Excel, it sounds like you found the right solution.
Take care,
JP
Thanks for your comment JP. I was looking for a simple way that would solve my problem without having to resort either to manual methods or to writing VBA code. I am sure you could come up with some code or macro that could solve this problem, but then Excel’s versatility is such that, as you said, there are usually 20 ways to do one thing.