Is there a way to select multiple delimiters (not provided by the DataTools Wizard) when splitting the data within a cell? Excel Data Tools Wizard provides Tab, Semicolon, Comma, Space and ONE other delimiter to separate data within one cell. But if my data contains 1/2|2\a-b-d, is there a way to split the data into individual elements ? Thank you very much.
Try this in B1 and pull formula to the right until you see blanks
=TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"/","-"),"|","-"),"\","-"),"-",REPT(" ",50)),50*COLUMNS($A:A),50))
Row\Col 1/2|2\a-b-d 1 2 2 a b d
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I'd probably use a text editor on your data and change one of the odd delimiters to one that is more Excel friendly. Something like, edit, find, replace all. Save as text, and do the Data Tools Wizard thingey.
Pete
Or this formula:
=MID($A$1;2*COLUMN()-3;1)
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks