Parsing Data from Cells

Text to Columns

Data Menu, Text to columns, Delimited, tick other delimiter, and enater |. The numbers without a | will stay in the column, the ones with a | will split in two. Is this what you want?
Richard

Re: Text to Columns

No I need each entry to go down to a new row that also copies the rest of the row to the new row. So I need to break up each number be a new record with each having a unique serial number and the other data just copied in the other cells.

Re: Text to Columns

> No I need each entry to go down to a new row that also copies the rest of the row to the new row. So I need to break up each number be a new record with each having a unique serial number and the other data just copied in the other cells.

Care to provide 5 rows of your data along with the results that you want to have?

Aladin

Re: Text to Columns

For Instance if I have:

Item Serial # Purch Date
Compaq ML370 123 10/10/01
Compaq v1100 234|267 10/01/01
HP LJ4100 567|789|890 10/05/01

I want it to change to:

Item Serial # Purch Date
Compaq ML370 123 10/10/01
Compaq v1100 234 10/01/01
Compaq v1100 267 10/01/01
HP LJ4100 567 10/05/01
HP LJ4100 789 10/05/01
HP LJ4100 890 10/05/01

Sollicite for VBA code unless the following holds...

If you have an independent list of serial numbers and the same serial number does not occur more than once in column B, a simple formula-based solution exists for the task.

Assuming that A1:C4 houses your sample data

{"Item","Serial #","Purch date";"Compaq ML370",123,37174;"Compaq v1100","234|267",37165;"HP LJ4100","567|789|890",37169}

Put the serial list in E from E2 on.

In D2 enter: =IF(AND(LEN(E2),SUMPRODUCT((ISNUMBER(SEARCH(E2,$B$2:$B$4))+0))>0),INDEX(A:A,SUMPRODUCT((ISNUMBER(SEARCH(E2,$B$2:$B$4))+0)*(ROW($B$2:$B$4)))),"")

Copy down as needed.

In F2 enter: =IF(LEN(D2)>1,INDEX(C:C,SUMPRODUCT((ISNUMBER(SEARCH(E2,$B$2:$B$4))+0)*(ROW($B$2:$B$4)))),"")

Copy down as needed.

If the conditions stated at the beginning do not hold, I'd suggest solliciting for VBA code for the task.

Regards,

Aladin

========

Extending the proposed formula-based solution

MJF,

Below I extend the formula-based solution that I proposed above. What follows is fully self-contained.

I reiterate one caveat:

The same serial number must not occur more than once in column B for all this to work properly.

I'll assume that A1:C4 houses the sample data:

{"Item","Serial #","Purch date";"Compaq ML370",123,37174;"Compaq v1100","234|267",37165;"HP LJ4100","567|789|890",37169}

You want to have as result:

{"Compaq ML370",123,37174;"Compaq v1100",234,37165;"Compaq v1100",267,37165;"HP LJ4100",567,37169;"HP LJ4100",789,37169;"HP LJ4100",890,37169}

The big numbers are the internal representations of data, so don't worry about them.

In D2 enter: =IF(AND(LEN(E2),ISNUMBER(SEARCH("|",B2))),SUBSTITUTE(B2,E2&"|","")&"|"&B3,B3)

In D3 enter: =IF(AND(LEN(E3),ISNUMBER(SEARCH("|",D2))),SUBSTITUTE(D2,E3&"|","")&IF(LEN(B4),"|"&B4,""),"")

In E2 enter: =IF(AND(LEN(B2),ISNUMBER(SEARCH("|",B2))),IF(LEN(B2)-LEN(SUBSTITUTE(B2,"|",""))+1>1,LEFT(B2,SEARCH("@",SUBSTITUTE(B2,"|","@",1))-1),B2),B2)

In E3 enter: =IF(LEN(D2),IF(LEN(D2)-LEN(SUBSTITUTE(D2,"|",""))+1>1,LEFT(D2,SEARCH("@",SUBSTITUTE(D2,"|","@",1))-1),D2),"")

Select D3:E3 and copy down as far as needed.

In F2 enter: =IF(AND(LEN(G2),SUMPRODUCT((ISNUMBER(SEARCH(G2,$B$2:$B$4))+0))>0),INDEX(A:A,SUMPRODUCT((ISNUMBER(SEARCH(G2,$B$2:$B$4))+0)*(ROW($B$2:$B$4)))),"")

In G2 enter: =IF(LEN(E2),E2+0,"")

In H2 enter: =IF(AND(LEN(G2),SUMPRODUCT((ISNUMBER(SEARCH(G2,$B$2:$B$4))+0))>0),INDEX(C:C,SUMPRODUCT((ISNUMBER(SEARCH(G2,$B$2:$B$4))+0)*(ROW($B$2:$B$4)))),"")

Note. The arg of INDEX is A:A in F2 and C:C in H2. These require that you don't have anything else but the data of interest in A thru C. Otherwise, change A:A to $A$1:$A$4 and C:C to $C$1:$C$4.

Select F2:H2 and copy down as far as needed.

Regards,

Aladin