Extracting comma seperated data from in one cell so that it will
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 varying
lengths (and they may not be populated to their max or are not populated
at all). Is there a formula I could use in the destination cells to
extract the required piece of data.

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

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


--
guyvanzyl
------------------------------------------------------------------------
guyvanzyl's Profile: http://www.excelforum.com/member.php...o&userid=35927
View this thread: http://www.excelforum.com/showthread...hreadid=558186

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=558186

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


--
guyvanzyl
------------------------------------------------------------------------
guyvanzyl's Profile: http://www.excelforum.com/member.php...o&userid=35927
View this thread: http://www.excelforum.com/showthread...hreadid=558186

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=558186