Is there a formula for...

[guyvanzyl]

Extracting comma seperated data from in one cell so that it wil
populate in another? The data may be various lengths or be empty.

eg: 123,,2345,67894,1,,3

The file I am currently working with has 23 fields each of them varyin
lengths (and they may not be populated to their max or are not populate
at all). Is there a formula I could use in the destination cells t
extract the required piece of data.

I have tried some formulas (MID), however they only seem to only wor
for fixed lengths.

i'm really struggling to crack this one despite some helpful guidance

 

[Dav]

If the data is already in the spreadsheet makes sure the columns to the
right of it are blank, then data_Text to columns delimited and select
comma

Regards

Dav

 

[guyvanzyl]

Problem is my data is arranged in rows as it is not practical for me to
do it in columns, so that method does not work

 

[Dav]

If your data is in lets say cells b3, d3,e3 etc
and a typical cell is as in your example and you wish to have the split
data in cells e4, e5, e6 etc for all of column E

It assumes that the characters $ and £ do not appear in your data,
if they do you need to find unique characters that do not


in e4 put
=LEFT(B$3,SEARCH(",",B3)-1)
In e5and copy down
=MID(SUBSTITUTE(SUBSTITUTE($B$3,",","$",ROW(A1)),",","£",ROW(A1)),SEARCH("$",SUBSTITUTE(SUBSTITUTE($B$3,",","$",ROW(A1)),",","£",ROW(A1)))+1,SEARCH("£",SUBSTITUTE(SUBSTITUTE($B$3,",","$",ROW(A1)),",","£",ROW(A1)))-SEARCH("$",SUBSTITUTE(SUBSTITUTE($B$3,",","$",ROW(A1)),",","£",ROW(A1)))-1)

in e26 your final column put
=MID(SUBSTITUTE($B$3,",","$",ROW(A23)),SEARCH("$",SUBSTITUTE($B$3,",","$",ROW(A23)))+1,LEN(B$3)-SEARCH("$",SUBSTITUTE($B$3,",","$",ROW(A23))))

Regards

Dav