Using VBA (or not) to concatenate cells with criteria

[Pouky]

Hi all,

I need to concatenate strings with the criteria that the text starts with :86: untill :61:.

For below example every new line is a new row in excel and everything is in column A. in B1 I would like to see the concanate of A1&A2&A3 since in A4 the line starts with :61:

I tried to do this with a formula but I couldn't figure it out. I think the best way would be a macro but I am open to suggestions.

A	B
:86:/EREF/1aliubdvliausbvliubasvuilbauislvbdlbasdlvbalisubvd <tbody> </tbody>	:86:/EREF/1aliubdvliausbvliubasvuilbauislvbdlbasdlvbalisubvdlasduvbalsbvlabsdliuvbialsbdvilubaslvbdluiabsvdasoudvilabusvdlibaslivb <tbody> </tbody>
lasduvbalsbvlabsdliuvbialsbdvilubaslvbdluiabsvd <tbody> </tbody>
asoudvilabusvdlibaslivb <tbody> </tbody>
:61:1505130513C13,43NTRFEREF//00000000096560 <tbody> </tbody>
/TRCD/00106/ <tbody> </tbody>
:86:/EREF/11aliubdvliausbvliubasvuilbauislvbdlbasdlvbalisubvd <tbody> </tbody>	:86:/EREF/11aliubdvliausbvliubasvuilbauislvbdlbasdlvbalisubvdlasduvbalsbvlabsdliuvbialsbdvilubaslvbdluiabsvdasoudvilabusvdlibaslivbridge AG/ <tbody> </tbody>
lasduvbalsbvlabsdliuvbialsbdvilubaslvbdluiabsvd <tbody> </tbody>
asoudvilabusvdlibaslivb <tbody> </tbody>
ridge AG/ <tbody> </tbody>
:61:1505130513C13,27NTRFEREF//00000000096588 <tbody> </tbody>

<tbody>
</tbody>

I am using excel 2010 on windows 7.

 

[Pouky]

Hi all,

I know nobody replied but I wanted to update you that I resolved this myself but in a different way.

I haven't provided you with all the details but that is because I didn't have all the details myself. It appeared that I was tackling this issue from a different approach. I tried to concatenate between to values but I could just do it BEHIND one value and ignore the excess information.
Since I always need to concatenate the 4 cells starting from :86: I used the following formula.

=IF((FIND(":86:",A1))=1,A1&A2&A3&A4,"")

There is probably a much cleaner/better/etc. way of doing this but this seems to do the trick.