MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Text to Columns Formula


Posted by Tom Packert on March 30, 2001 3:07 PM

Hello,

I have looked all over for a quicker way to implement a way to extract fields from a delimited string.

I have data being returned from a queries that looks like this:

1500;0;0;0;0;0;0;0;0;0;0;0;0

I need to break these out into the Separate Values. I know I can use the TexttoColumns method in a Macro, but I was hoping there was a hidden formula that does this like the Notes @WORD() function.

The search and MID functions can be combined to do this but is gets really ugly. There has got to be a better way.

Its late and its friday so... Any help is appreciated!


Posted by Bruce on March 30, 2001 5:24 PM

Save the query as a text file. Open it in Excel, the text import wizard should open asking for delimited or fixed width file type, select delimited, then select semicolon as the delimiter.