Extracting data

[Daniel Bonallack]

In column A, in each cell I have up to 30 banks separated
by a semicolon. In column B, in each cell I have up to
thirty sales figures, separated by a semicolon (so each
bank has a sales figure).

So A1: "BankA; BankB; BankC; BankD;" etc
and B1: "23.7;22.8;12.6;14.6;" etc

I want to extract the sales figure for BankC into cell C1
with a formula. (I don't want to do Text-To-Colums). So
in this case, C1 would have 12.6.

Is this possible, either with a formula or simple code?

Thanks

 

[Don Guillett]

try this for b1 to find the 2nd ; and the 3rd ; and use mid for in between.
Yields 12.6
=MID(b1,FIND("^",SUBSTITUTE(b1,";","^",2))+1,LEN(b1)-FIND("^",SUBSTITUTE(b1,
";","^",3)))

 

[Daniel Bonallack]

Thanks Don

But I forgot to say that the bank I'm looking for
(Deutsche Bank) might be listed as the third bank in cell
A1, but the 8th bank in cell A2.

Is there another way?

Daniel

 

[Harlan Grove]

...
...

But I forgot to say that the bank I'm looking for
(Deutsche Bank) might be listed as the third bank in cell
A1, but the 8th bank in cell A2.

Is there another way?

...

It gets much uglier. First, define a name like Seq referring to

=ROW(INDIRECT("1:1024"))

Then try the array formula

=MID(LEFT(B1,SMALL(IF(MID(B1&";",Seq,1)=";",Seq),
SUM(--(MID(A1,Seq,(Seq<FIND("BankC",A1)))=";"))+1)-1),
SMALL(IF(MID(B1,Seq,1)=";",Seq),
SUM(--(MID(A1,Seq,(Seq<FIND("BankC",A1)))=";")))+1,1024)