@ > Home > Contents > Split string formula into sub strings of variable length Excel 97+

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 #.