Parsing Data from a String based on an adjacent cell 1

[romeerome368]

Hello Everyone,

I have trying to parse a value from a string based on the value of an adjacent cell in Excel 2010.

My data looks as follows:

UOM[tab]Package Content
EA[tab][tab]EA-1;BX-10;CA-40
BX[tab][tab]EA-1;BX-10;CA-40
CA[tab][tab]EA-1;BX-10;CA-40

I am trying to use SEARCH in addition to other formulas to find the UOM value in the Package Content data. This formula, =SEARCH(UOM,Package Content), tells me where the UOM match begins in Package Content, but I don't know how to string the other formulas together to get the desired results.

1st Example: My first UOM value is EA, and the Package Content value that corresponds to EA is 1. So I'm trying to come up with a formula to return 1.
2nd Example: My second UOM value is BX, and the Package Content value that corresponds to BX is 10, and so on.

I have been pulling my hair out and searching the internet, and trying to develop this formula myself to no avail.

Thanks in advance to anyone that can provide some assistance to me.

 

[SkipVought]

HI,

On the Package Content column use Data > Text to columns > DELIMITED ...and use ;

This will put the data into 3 columns.

You can then do Text to columns on each of these using - as the delimiter AND not importing the first column.

You'll end up with just numbers in three columns. Make the three headings EA, BX & CA, and one formula will work for you.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[romeerome368]

Thanks Skip. I guess I was making it too difficult.

 

[SkipVought]

So here's the table after the parsing process using Text to columns...
[pre]
UOM     EA      BX      CA     
EA      1 10 40
BX      1 10 40
CA      1 10 40
[/pre]

Then I added a column for a formula
[pre]
UOM     EA      BX      CA      UM
EA      1 10 40 1
BX      1 10 40 10
CA      1 10 40 40
[/pre]
and the formula...
[tt]
E2: =INDEX(B22,1,MATCH(A2,$B$1:$D$1,0))
[/tt]
...and copy/paste down


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[romeerome368]

Skip,

So, if the data look like this:

UOM[tab][tab]Package Content
EA[tab][tab][tab]EA-1;BX-10;CA-40
CA[tab][tab][tab]BX-10;EA-1;CA-40
BX[tab][tab][tab]CA-40,BX-10;EA-1

What would the formula look like now?

This makes that it a little bit more difficult to parse out that data.

I discovered this as I was looking further down the column.

 

[SkipVought]

This row has a COMMA rather than a SEMICOLON. Is that the situation: COMMAS and SEMICOLONS as well as different units of measure on each line?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

So if you parse (text to columns) the Package Content DELIMITED using... SEMICOLON, COMMA, and DASH (; , -) you get these seven columns and I added the Eighth FORMULA in [tt][highlight #FCE94F]H2[/highlight]: =INDEX(B2:G2,1,MATCH(A2,B2:G2,0)+1)[/tt]
[pre]
UOM Pkg Ct UM
EA EA 1 BX 10 CA 40 [highlight #FCE94F]1[/highlight]
CA BX 10 EA 1 CA 40 40
BX CA 40 BX 10 EA 1 10
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[romeerome368]

I was trying to do something like this.

I parsed out the data like below:

UOM[tab][tab]Column1[tab][tab]Column2[tab][tab]Column3
EA[tab][tab][tab]EA-1[tab][tab][tab][tab]BX-10[tab][tab][tab][tab]CA-40
CA[tab][tab][tab]BX-10[tab][tab][tab][tab]EA-1[tab][tab][tab][tab]CA-40
BX[tab][tab][tab]CA-40[tab][tab][tab][tab]BX-10[tab][tab][tab]EA-1

=IFERROR(TRIM(IF(FIND(UOM,Column1),MID(Column1,FIND("-",Column1)+1,99),IF(FIND(UOM,Column2),MID(Column2,FIND("-",Column2)+1,99),IF(FIND(UOM,Column3),MID(Column3,FIND("-",Column3)+1,99)))),"")

It works for the first one, but it doesn't work for the 2nd and 3rd lines.

 

[romeerome368]

Skip,

You are awesome dude!! You taught a great deal through this post.

Thank you sooooo much!!!

 

[SkipVought]

Where abouts are you. DFW area here.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

romeerome368,
Your issue would be a good problem to resolve with UDF (User Defined Function) in Excel. Granted, it would need a little of VBA code, but you could use it as any other (build-in) function in Excel. You may find out a lot of use of 'custom' easy to modify UDF's.

Just a suggestion...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Are these three ALWAYS the same or could you have other UOMs or other Qtys?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]