Help With Vehicle Fitment Cell

Is the below all in one cell?

2007,2017,Jeep,Wrangler,|2007,2016,Jeep,Wrangler,Unlimited Rubicon|2007,2016,Jeep,Wrangler,Unlimited Sahara|2007,2010,Jeep,Wrangler,Unlimited X|2010,2016,Jeep,Wrangler,Unlimited Sport|2011,2011,Jeep,Wrangler,Unlimited 70th Anniversary|2016,2016,Jeep,Wrangler,Unlimited 75th Anniversary|2016,2016,Jeep,Wrangler,Unlimited Sport S|2018,2018,Jeep,Wrangler JK,

if not please add a sample worksheet so we know exactly what you are dealing with. See yellow banner at top for instructions

Lets say all that data is in cell A1.
select A1 and then go to DATA ribbon and then TEXT TO COLUMNS
Select Delimited and then hit Next.
Only have "OTHER" checked and then in the space next to it type a "|" without quotations. this is the vertical line by the way the one you hit on your keyboard above the enter button with shift.
Hit Finish

You will now have the individual lines split into columns on row 1.
now in cell A2 paste this array formula. (as this is an array formula you must enter it with CTRL+SHIFT+ENTER. If done so correctly { } will be put around your formula.

Formula:

Please Login or Register  to view this content.

Once this is done drag this formula all the way over to the right.
In cell A4 paste this standard formula.

Formula:

Please Login or Register  to view this content.

This should give you the outcome of all years listed for each individual automobile in one cell.

good luck

ill hopefully have time to take a look at this on monday

Well this was fun! To be honest I'm not that good at (comfortable, confident etc.) working with arrays in VBA. I do however enjoy array formulas. So this spectacle of a solution uses Textjoin both horizontally and vertically on a separate sheet to solve the task but only for one cell. VBA is then used to loop through and bounce each cell off the calculation sheet and back.
I'm sure some people here would rather swim with crocodiles than using a wacko macro like this but overall I'm still satisfied with how it turned out.

Please Login or Register  to view this content.

All the formulas look like this:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

(array this one)

Formula:

Please Login or Register  to view this content.

Edit: reuploaded, there were some bugs

Derwoods post above #9, did a fantastic job at using your data and showing step by step directions on how the formulas will work. If you want a non macro-way i would follow his post.

@hrlngrv i was unaware of the SEQUENCE function. Upon looking at the office website, it is still in beta and is only available to Office insiders, which i guess the company i work for is not. While reading over the functions makeup it does indeed look like when this function is released to the public it will offer much versatility and could definitely be applied for the solution cjs was looking for.

https://support.office.com/en-us/art...4-2eb78519ca90

Something like this:

Just a second...

Ok this will be more than a second but I'll look into it now. One of these days I need to learn to read the full text of a post instead of just every 25 word....

Ok so I tweaked it to do something that hopefully resembles a solution.
Same VBA loop, just a bit different array formula.

Thansk! Well as of now, no. However it is a good idea, I'll try to applicatifize it a bit and implement that. I'm going into a busy period so it may take a day or two.
If you have to do things now you are better of copying a sheet of data into this workbook and rename it "data". You can run the macro from Alt + F8

This should do the trick:

Yeah I was afraid of that. It works on my PC a couple of times and then corrupts. I downloaded the file I posted, ran the macro successfully 3 times then crash. It seems it didn't like when I put the code in the sheet module as you requested. In general I feel like it's still 2016 and this guy nailed it: https://www.reddit.com/r/Windows10/c...on_windows_10/

Edit: I just reverted back to modules and it keeps crashing...

Originally Posted by cjs1031

What would be the easiest way in this scenario to remove the extra year and comma?

Example:
2011,2011,Jeep,Wrangler,70th,Anniversary|2016,2016,Jeep,Wrangler,75th,Anniversary

What it should look like:
2011,Jeep,Wrangler,70th,Anniversary|2016,Jeep,Wrangler,75th,Anniversary

Not sure what you mean here. Doesn't it work as intended in post 20?