Task:

Split a string into adjacent cells. A formula has to be used, not a Split or Import Assistant.

A2: 2.3#30000#Grant#3/31/2008#5003.67#

Solution (Klaus "Perry" Pago 2007/06/15):

B2:F2: =MID("#"&$A2,
FIND("_",SUBSTITUTE("#"&$A2,"#","_",COLUMN(A2)))+1,
FIND("_",SUBSTITUTE("#"&$A2,"#","_",COLUMN(B2)))-
FIND("_",SUBSTITUTE("#"&$A2,"#","_",COLUMN(A2)))-1)

- is not a array
- must be copied into one cell like B2
- and then be filled in until F2

SUBSTITUTE is the only text function (before xl2007) using the argument "instance n°". Originally needed here for FIND, it gets converted by the usage of SUBSTITUTE. To convert to numbers instantly, proceed the formula with a -- (double minus). This also works for data like 3/31/2008, then simply being converted into serial numbers.

What, if the sub strings have a variable count? Then:

B2:F2: =IF(COLUMN(A2)<=LEN($A2)-LEN(SUBSTITUTE($A2,"#","")),MID("#"&$A2,
FIND("_",SUBSTITUTE("#"&$A2,"#","_",COLUMN(A2)))+1,
FIND("_",SUBSTITUTE("#"&$A2,"#","_",COLUMN(B2)))-
FIND("_",SUBSTITUTE("#"&$A2,"#","_",COLUMN(A2)))-1),"")

Sundry 1:

Bernd Plumhoff, 2007/06/16, offers a VBA function array. Select B2:F2 first, and then type it by CTRL-SHIFT-ENTER, as you would do for a simple array formula in B2 only.

B2:F2: =wssplit(A1,"#")

Function wssplit(s As String, _
Optional sdelimiter As String = ".", _
Optional lcount As Long = -1) As Variant
wssplit = Split(s, sdelimiter, lcount)
End Function

Sundry 2:

The fields as in 3,4#MEI#600#723# have the same length of 3:

B2: =MID($A2,COLUMN(B2)*4-7,3)

Note: In such a case you would not need the separators #.