Hello Everyone,

I am in need to some assistance with an issue I am having. I am getting data from a customer that has automobile information placed within one cell. Each Cell has the data compiled in this manner:

Year|Make|Model|Trim|Engine Size [Carriage Return]
Year|Make|Model|Trim|Engine Size [Carriage Return]
etc...

I am in need of a macro, VB, or formula that can go through the cell and run a formula on each line that is broken up by the carriage return. I need to have this formula or macro provide me with the first three points of data for each line concatenated together. This is how I would need the data compiled

Year|Make|Model::|Year|Make|Model::etc....

Would a macro be the best possibility of this. As of right now, I only have the left formula that is providing me with the first line of data in the correct format. The formula is:

=LEFT(A2,FIND("!",SUBSTITUTE(A2,"|","!",3)) -1)
Could something loop this formula through and use it on each line? I am trying to figure something out, but it has been a struggle. I would also like the macro or VB to dedup the concatenation after it is done, so if I have this for the data sets:

2007|Jeep|Grand Cherokee|Laredo Sport Utility 4-Door::EXCLUDES SRT-8
2007|Jeep|Commander
2007|Jeep|Grand Cherokee|Limited Sport Utility 4-Door::EXCLUDES SRT-8
2007|Jeep|Grand Cherokee|Overland Sport Utility 4-Door::EXCLUDES SRT-8
2006|Jeep|Commander

And I only need the first three points of data in each line with their separator, I would get this in the concatenation:

2007|Jeep|Grand Cherokee::|2007|Jeep|Commander::|2007|Jeep|Grand Cherokee::|2007|Jeep|Grand Cherokee::|2007|Jeep|Grand Cherokee::|2006|Jeep|Commander

Would it be possible to just take out two of the "2007|Jeep|Grand Cherokee" listings, so it leaves only one?

Any assistance is appreciated.