am trying to separate out the contents of a single cell within Excel
into multiple cells. The text contents within the cell have been input
in a columnar format within the cell. I have tried a number of similar
solutions that I have found on this site but nothing is working. The
following is an example of the two types of text I am trying to
separate out. Each entry within the cell was stacked above each other
with a return. Not all cells within the spreadsheet are going to have
the same product number length.

In Cell A1

30092-08/16
30093-08/16
60236-08/16
60237-08/16

In Cell B1

Net Timer, Hip Stem, Concensus, TI, Sz 08/16
Net Forging, Hip Stem, Consensus, CoCr, Sz 08/16
Overlay, 5x Scal, Net Forging, Hip Stem,
Consensus, Ti, Sz 08/16


--
mcertini
------------------------------------------------------------------------
mcertini's Profile: http://www.excelforum.com/member.php...o&userid=27151
View this thread: http://www.excelforum.com/showthread...hreadid=466632

Hi mcertini,

If your first stacked value is in [A1] then introduce the following ARRAY
formula (confirm with Ctrl+Shift+Enter, not just Enter) in [B1] and copy to
the right and downwards:

=INDEX(MID($A1,1+LARGE((MID($A1&CHAR(10),ROW(INDIR ECT("1:"&LEN($A1)+1)),1)=CHAR(10))*ROW(INDIRECT("1 :"&LEN($A1)+1)),1+ROW(INDIRECT("1:"&LEN($A1)-LEN(SUBSTITUTE($A1,CHAR(10),""))+1))),LARGE((MID($ A1&CHAR(10),ROW(INDIRECT("1:"&LEN($A1)+1)),1)=CHAR (10))*ROW(INDIRECT("1:"&LEN($A1)+1)),ROW(INDIRECT( "1:"&LEN($A1)-LEN(SUBSTITUTE($A1,CHAR(10),""))+1)))-LARGE((MID($A1&CHAR(10),ROW(INDIRECT("1:"&LEN($A1) +1)),1)=CHAR(10))*ROW(INDIRECT("1:"&LEN($A1)+1)),1 +ROW(INDIRECT("1:"&LEN($A1)-LEN(SUBSTITUTE($A1,CHAR(10),""))+1)))-1),1+LEN($A1)-LEN(SUBSTITUTE($A1,CHAR(10),""))-(COLUMN()-COLUMN($B:$B)))

Regards,
KL


"mcertini" wrote in
message ...

am trying to separate out the contents of a single cell within Excel
into multiple cells. The text contents within the cell have been input
in a columnar format within the cell. I have tried a number of similar
solutions that I have found on this site but nothing is working. The
following is an example of the two types of text I am trying to
separate out. Each entry within the cell was stacked above each other
with a return. Not all cells within the spreadsheet are going to have
the same product number length.

In Cell A1

30092-08/16
30093-08/16
60236-08/16
60237-08/16

In Cell B1

Net Timer, Hip Stem, Concensus, TI, Sz 08/16
Net Forging, Hip Stem, Consensus, CoCr, Sz 08/16
Overlay, 5x Scal, Net Forging, Hip Stem,
Consensus, Ti, Sz 08/16


--
mcertini
------------------------------------------------------------------------
mcertini's Profile:
http://www.excelforum.com/member.php...o&userid=27151
View this thread: http://www.excelforum.com/showthread...hreadid=466632

I suppose that the entries are stacked above each other with a CHR(10)
(Alt-Enter).
I suggest first to replace CHR(10) characters to a non used character, e.g.
semicolon with the following one line macro:

Sub replchr10()
Cells.Replace What:=Chr(10), Replacement:=";", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
End Sub

Then apply Data-Text to columns with a ; as separator.

Regards,
Stefi


€žmcertini€� ezt Ã*rta:


am trying to separate out the contents of a single cell within Excel
into multiple cells. The text contents within the cell have been input
in a columnar format within the cell. I have tried a number of similar
solutions that I have found on this site but nothing is working. The
following is an example of the two types of text I am trying to
separate out. Each entry within the cell was stacked above each other
with a return. Not all cells within the spreadsheet are going to have
the same product number length.

In Cell A1

30092-08/16
30093-08/16
60236-08/16
60237-08/16

In Cell B1

Net Timer, Hip Stem, Concensus, TI, Sz 08/16
Net Forging, Hip Stem, Consensus, CoCr, Sz 08/16
Overlay, 5x Scal, Net Forging, Hip Stem,
Consensus, Ti, Sz 08/16


--
mcertini
------------------------------------------------------------------------
mcertini's Profile: http://www.excelforum.com/member.php...o&userid=27151
View this thread: http://www.excelforum.com/showthread...hreadid=466632