Split / Explode Cell into Multiple Outcomes

[booYEAH]

I have many, many cells with data of various sorts which needs to be split out into individual cells. Each line item below is separated by "Alt+Enter", however not all cells are created equally (cells may have zero to eight entries).

"X-1
01-Aug-2011
PTC
01-Aug-2011
UPV0
01-Aug-2011
FC5
01-Aug-2011
M-1DJ
01-Aug-2011"

Essentially, I'd like to look in a row and if a certain text string exists (ie. PTC), then return the date immediately following (in the example above, would be 01-Aug-2011).

Thanks in advance!

 

[Desu Nota from Columbus]

For the example you provided :

=MID(A1,FIND("PTC",A1)+4,11)

Would return 01-Aug-2011


Not a universal solution, but hopefully this helps.

You might want to try using Text to Columns (under Data), you might be able to delimit based on a new line.

 

[booYEAH]

In trying Text to Columns, I could only split into two new columns. That would be the best option, but it recognizes the first Return and none thereafter.

 

[Kentman]

Essentially, I'd like to look in a row and if a certain text string exists (ie. PTC), then return the date immediately following (in the example above, would be 01-Aug-2011).

You will need to look for the Alt+Enter e.g FIND(Char(10)) that appears after the particular string, you can then use this function to figure out where the EOL character is within the string. If you have specific strings i.e. "PTC" you can use the example Desu Nota illustrates along with the above, especially if the string is of variable length.

 

[booYEAH]

OK - I have six unique strings I'm searching for and returning a value if it exists. Would I use both the FIND(Char(10)) and FIND("PTC") formulas?

 

[WhatTheXL]

Try

=IF(ISERR(FIND(CHAR(10),RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1,FIND(B1,A1))))),RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1,FIND(B1,A1))),LEFT(RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1,FIND(B1,A1))),FIND(CHAR(10),RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1,FIND(B1,A1))))-1))

if your data are in A1 Put "PTC" in B1 and The above formula in C1.
it will work as long as the B1 String is in A1.

A bit nasty but it works.

 

[booYEAH]

WhatTheXL, appreciate the help. That certainly does what I need done.

To complete the spreadsheet automation, I am now left looking across a row of many columns to find a date. Once a date is found, I need to return the value in the header row of that column.

Example:

Col B -- C -- D -- E -- F -- G
MR -- PS -- PSC -- PTCC -- PTC -- PA
#VALUE! -- 19-Sep-2011 -- #VALUE! -- #VALUE! -- #VALUE! -- #VALUE!

A2 would look across Row 2, find the date (only one date in a row) and return the value in Row 1 (PS in the above example).

A nested IF statement would work, but I need 10 of them and Excel 2003 only allows 7.

 

[WhatTheXL]

I apologize for the delay. I've been out the last few days.

TRY:

=INDEX($B$1:$K$1,1,MATCH(1,INDEX(--ISNUMBER(B2:K2),1,0),0))

Adjust the range as needed, make sure your header row has an absolute reference B$1:K$1

The row where the date is being located needs a relative reference B2:K2

This will work as long as there is only 1 Date and no other numbers on that row, the #Value errors are OK.
If there is another number on that row it will use the first column that has a number / date.