MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Parsing Data from Cells


Posted by MJF on October 09, 2001 7:08 PM

I have a worksheet where one of the columns contains serial numbers. Some of the cells in the column contain one number and others contain two or more numbers separated by a "|" (pipe). I need to parse these out to separate records with one number in each cell and copy the other cells from that record to this record. Any ideas?


Posted by Richard S on October 09, 2001 8:56 PM

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

Posted by MJF on October 10, 2001 7:27 AM

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.


Posted by Aladin Akyurek on October 10, 2001 7:40 AM

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

Posted by MJF on October 10, 2001 8:27 AM

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

Posted by Aladin Akyurek on October 10, 2001 12:17 PM

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

========

Posted by Aladin Akyurek on October 13, 2001 11:22 AM

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