Extracting data

  • Thread starter Thread starter Daniel Bonallack
  • Start date Start date
D

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
 
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)))
 
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
 
...
...
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?
Click to expand...
...

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)
 
Back
Top