Quick Text to Columns Utility

I was trying to get a list of file names from a list of paths I had in column A. I used Data - Text to Columns delimited on “\”. The results are below. I highlighted the file names so you could see my problem.

There were only a dozen or so paths, so it didn’t take long to get all the file names in the same column. But it got me thinking about a right-to-left feature for TTC. I have one more instance where it would be useful. In Quickbooks, the inventory items’ full names look like this:

Sales:Hardware:Electronics:Item1
Sales:Hardware:Item2
Sales:Software:ShrinkWrap:Item3

A right-to-left feature would be useful there too. As I started planning out this utility (that means I was really looking at the built-in feature to copy what I could), I couldn’t help notice some features I never use. First, there is no reason this needs to be a three form wizard – it should all be on one form. Second, I very rarely set the column data types, just accepting General. Last, I never noticed the Destination refedit on the last form. Only when I took a hard look at this process did I notice it was there. Seems like a fairly useful feature, but I haven’t used it 25 years.

I’ve been enjoying my DDoE Quick Find so much lately, I decided to make a Quick TTC utility that got rid of the crap and added the right-to-left feature. Here’s what I ended up with.

Some features include:

  • No wizard. It’s all on one screen
  • Right to left option as shown above
  • Custom delimiters (there’s two) can be multiple characters
  • No field types, no destination option

I’ll be blogging about some of the code later this week, but if you want to check it out on your own and can download DDoEQuickTTC.zip. It’s in serious BETA, so use at your own risk.

11 thoughts on “Quick Text to Columns Utility

  1. Dick,

    If all you wanted was the file name (the right-most part), an easy (non-programmatic) way of achieving this is to have performed a Search and Replace (ctrl-H). Replace “*” with [Nothing]. The result would have been just the file name. I often use this “trick” when I need to parse “Last Name, First Name” combinations.

    David

  2. Don’t mean to rain on your “quick utility” parade, Dick, but with this one you’ve lost me.

    The layout of the data from the right-to-left approach, with the exception of the last column, provides no information that is easily usable — at least as far as I can tell. And, of course, the “last column” will shift from one folder to another depending on how many sub-folders there are in the starting folder. If you must stick with this approach, reverse the layout. File-name, parent, parent-of-parent, …, disk-drive. Still not very useful but at least column 1 is now known with certainty.

    Also, going down the code path is an invitation for “yes, that’s good but how about this feature…” enhancements to the code…you’ll land up with another wizard. {grin}

    I would simplify the problem. Given a list of full names extracting the last token is pretty easy. If you must use code, just use the VB InStrRev function (either in a subroutine or — and this would be my preference — through a VBA wrapper function). And, of course, there is always the formula =MID(I5,FIND(CHAR(255),SUBSTITUTE(I5,””,CHAR(255),LEN(I5)-LEN(SUBSTITUTE(I5,””,””))))+1,255) where “” is the delimiter of interest and I5 contains the full filename (or any other “complete identifier”)

    I could be wrong but in this case creating support functions will yield a richer and more reusable library than writing a quick utility. You could have a filename function, a parent-folder function, a disk-drive function, a nesting-depth function, etc. It will take less (or at least no more) time than the quick utility and provide a much better “return on investment.”

    From Seattle, MVP Summit, Day 1

  3. Very nice DK. All this tool development – do I smell an Add-In coming?

    For the quick Quick TTC utility, It may be nice to have the option of auto-shifting columns to the right instead the default Overwrite behavior.

    That is to say, the native Excel TTC warns me that it will overwrite columns with existing data. I have click the “no” option, adjust the columns myself, then go through the TTC wizard again. I would be nice to have an option where this automatically happens.

  4. Another formula based option, using MoreFunc.xll is

    =WMID(A1,WORDCOUNT(A1,””),1,””)

  5. I don’t get the usefulness of this exercise. The kind of granularity I need is splitting a full path and file name into (a) a path and (b) a file name. I can sort by file name for some purposes and by path for others. The jumble of what’s in which column in the multicolumn listbox would make me crazy.

  6. I hope you don’t mind if I suggest to left justified the posts. I’ve been reading this blog with my cell phone and it’s hard to do so because of it.

    By the way, nice blog. I’m learning a lot.

    Thanks

  7. Joprotus: I don’t know what you mean by left justified. I’ve made a mobile css, but it’s never worked and I don’t know why. I need someone who knows what they’re doing to help me with it.

  8. I find this utility very useful for a situation I run into a lot. I copy a table from a PDF document that has several columns of numbers on the right, with descriptions that contain a variable number of space separated words on the left. Your utility lines up the number columns perfectly, then a simple multicat of all the descriptor columns collapses them into a single column of descriptions. It’s already saved me quite a bit of time.

    Thank you very much for this.

  9. GREAT help! I just used it with Excel 2013 to move my text to columns from right to left after working on this for several hours to no avail. THANKS!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.