Split a line of words, each word in a separate cell using function

All,
I am looking for something similar as described in http://www.excelforum.com/excel-gene...rate-cell.html except that I am looking to code a user defined function, "SplitText" with 1 argument. My text are delimited by | but with various parts and sometime empty part. The difference is that I don't need to have row 1 to have the number as in the previous thread. Please help. Thanks.

Please Login or Register  to view this content.

Maybe to can achieve it with text to column.

Excel 2007 => data => text to column

could you copy the data to a new column away from your original data and try text-to-colums there and then just use what you need?

Here you go, put this function into a regular module (Insert Module), then use it as you've described as a formula function.
The function actually has 3 parameters, not two. You can specify the delimiter as a third option:

=SplitText(A1, 1)
=SplitText(A1, 1, "|")

If you leave out the third parameter, it defaults to the | character, but you can control it if you wish.

Please Login or Register  to view this content.

JBeaucaire,

I am blown away on the elegance of the code and your thoughtfulness on the 2nd argument. It makes the UDF more generic. I did 1 minor change i.e. MyNum + 1 to handle the last part correctly. Thanks a zillion.

Please Login or Register  to view this content.

Btw, if you know how to mark this thread as [SOLVED], please let me know.

Changing the -1 to +1 breaks the function in my testing.

In your original example strings, the delimiter is the first character as well as between. That's atypical, usually the first delimiter occurs between the first two strings.

Also, when creating an array (textARR) in the function, they are zero-based arrays, so the first field is called 0, not 1.

This works well in your atypical strings because the first character being a | means the first field in the array is empty, so your first string really is is in textARR(1), convenient.

But I realized you might occasionally have strings that DON'T have the starting character, a more typical reality. In that case, to get the first string, you would want textARR(0), not (1). So, I check, and if the first character is NOT the delimiter, I subtract 1 from the number in your second parameter.

So, your change should yield the 3rd string if the first character is missing.

Anyway, please test all that carefully.

See the THREAD TOOLS menu at the top of this thread.