How do I put a delimited text string into Excel Cells

[JackCopper]

I would like to put tab delimited text into successive horizontal cells. TextToColumns does what I want, except that it is a method of range objects, hence I seem to have to put the entire string into a one-cell range, then use TextToColumns on the range. It works, but it is ugly. Could someone offer some guidance for a better way.

Thanks,
Jack Copper

 

[xlbo]

Kinda depends where your text is coming from - if it's a text file, just use the import wizard for delimited text files - record it if you need the code.
HTH
Geoff

 

[mikewoodhouse]

How about splitting the string into an array and pumping the array into your target range?

I think the Split() VBA function is present from XL2000 onwards, prior to that, there is suitable code on msdn.microsoft.com (look for Q188007: you may need to go to "Advanced Search" and check "All Knowledge Base" and "Archived Items" to get it).

Dim arrVals
arrVals = Split(myString, vbTab) ' use Chr(9) if vbTab not defined
.Cells(myRow, myCol).Resize(1, UBound(arrVals) + 1) = arrvals

HTH,

Mike

 

[JackCopper]

Thanks, folks.

I finally decided I'd have to parse the string and insert values cell by cell (my add-in has to work with Excel 8/87, so I can't rely on Split).
I'm trying to break myself of the habit of writing all low-level stuff - I continue to find Excel methods that I didn't previously know about - so I hoped maybe I had missed a method that would automagically do what I wanted.

Jack