Mapping the data & the getting output

Dear Excel Users

I would like to get summary of the data from my excel data sheet.

For example my data looks like this

1032-hydrogen-bond-frame.dat.c.d.lw 1032-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51812, 'SPC', 'X8535G', 2432, 'GLN', 'A 179B', 51810, 'SPC', 'X8535G')]
948-hydrogen-bond-frame.dat.c.d.lw 948-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 51112, 'SPC', 'X8302G', 51113, 'SPC', 'X8302G', 3962, 'ASP', 'A 300B')]
810-hydrogen-bond-frame.dat.c.d.lw 810-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 26746, 'SPC', 'X 180G', 26747, 'SPC', 'X 180G', 3962, 'ASP', 'A 300B')]
915-hydrogen-bond-frame.dat.c.d.lw 915-hydrogen-bond-frame.dat.c.d.pw [(4904, 'UNK', 'Z 900B', 29603, 'SPC', 'X1132G', 29602, 'SPC', 'X1132G', 3962, 'ASP', 'A 300B')]
768-hydrogen-bond-frame.dat.c.d.lw 768-hydrogen-bond-frame.dat.c.d.pw [(47967, 'SPC', 'X7254G', 4947, 'UNK', 'Z 900B', 47969, 'SPC', 'X7254G', 2705, 'TYR', 'A 199B'), (4904, 'UNK', 'Z 900B', 66308, 'SPC', 'X3437G', 66307, 'SPC', 'X3437G', 3963, 'ASP', 'A 300B')]
920-hydrogen-bond-frame.dat.c.d.lw 920-hydrogen-bond-frame.dat.c.d.pw [(41106, 'SPC', 'X4967G', 4922, 'UNK', 'Z 900B', 41107, 'SPC', 'X4967G', 3841, 'ASN', 'A 293B'), (4904, 'UNK', 'Z 900B', 54491, 'SPC', 'X9428G', 2415, 'HIS', 'A 178B', 54489, 'SPC', 'X9428G')]


From the above data I would like tp map the number "4904 and the A XXXB" where XXX is any three alphabet. Since the datas are present in different column i'm having great difficult to get the data mapped in output. I'm having huge datas like the above.

So that the final data should like this

1032-hydrogen-bond-frame.dat.c.d.lw (4904 A179B)
948-hydrogen-bond-frame.dat.c.d.lw (4904 A300B)
810-hydrogen-bond-frame.dat.c.d.lw (4904 A300B)
951--hydrogen-bond-frame.dat.c.d.lw (4904 A300B)
768-hydrogen-bond-frame.dat.c.d.lw (4904 A300B)
920-hydrogen-bond-frame.dat.c.d.lw (4904 A178B)

I have attached my data for your reference.

I am not familar with macors programming. Any help is greatly appreciated.

Many Thanks
Balaji

Originally Posted by martindwilson

try
=LEFT(A1,FIND(" ",A1))&" (4904 "&MID(A1,SEARCH("A????B",A1),6)&")"
or to get rid of odd space at end
=LEFT(A1,FIND(" ",A1))&" (4904 "&SUBSTITUTE(MID(A1,SEARCH("A????B",A1),6)&")"," ","")

Dear Martin

Thanks a lot

It works fine.

Many Thanks
Balaji

Originally Posted by Richard Buttrey

Hi,

When there are more than one "AXXXB" in a cell, like for example the 5th & 6th examples, what determines which one you use? e.g. why A300B in the 5th example and not the earlier 'A199B'

And why do you mention "XXX" is any three alphabet when clearly the 3 characters are all digits?

Regards

Dear All

Thanks for reply.

Richard, there are many "AXXB", for example 5th example data it has "A199B' & "A300B".
However I want to include the one which accomapined with number 4904.

768-hydrogen-bond-frame.dat.c.d.lw 768-hydrogen-bond-frame.dat.c.d.pw [(47967, 'SPC', 'X7254G', 4947, 'UNK', 'Z 900B', 47969, 'SPC', 'X7254G', 2705, 'TYR', 'A 199B'), (4904, 'UNK', 'Z 900B', 66308, 'SPC', 'X3437G', 66307, 'SPC', 'X3437G', 3963, 'ASP', 'A 300B')]

Many Thanks
Balaji