Merging multiple years with car make and model in Google Sheets

Other Questions Google Sheets
1

Hey folks, I’m stuck on a Google Sheets problem. I’ve got a table with car info. Column A has years (sometimes multiple), B has the make, and C has the model. I want to combine these into tags in Column D.

For example:
Years: 1973,1974,1975
Make: Ford
Model: Mustang
Tags: 1973 Ford Mustang, 1974 Ford Mustang, 1975 Ford Mustang

I’ve tried asking AI for help, but the formulas they give me don’t work. Any ideas on how to make this happen? I’m not sure if I need a special function or some kind of loop. Thanks for any help!

2

hey davidw, i think i can help u out.

try this formula in D1:

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(REPT(B1&" “&C1&”,“,LEN(A1)-LEN(SUBSTITUTE(A1,”,“,”“))+1),”,“))&” “&TRANSPOSE(SPLIT(A1,”,"))))

lmk if u need any help with it!

3

I’ve dealt with similar data manipulation challenges in Google Sheets before. Here’s an approach that might work for you:

=ARRAYFORMULA(IF(A1:A<>“”,TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(B1:B&" “&C1:C&”,“,LEN(A1:A)-LEN(SUBSTITUTE(A1:A,”,“,”“))+1)),”,“))&” “&TRANSPOSE(SPLIT(A1:A,”,“))),”"))

This formula combines array functions to split the years, repeat the make and model for each year, and then concatenate everything together. It should handle multiple rows at once.

A few tips:
Make sure your data starts in row 1
Copy the entire formula exactly as written
Paste it into cell D1

If you run into any issues, double-check that your input data is formatted consistently. Let me know if you need any tweaks or explanations!

4

I’ve encountered a similar issue before. Here’s a solution that worked for me:

In cell D1, use this formula:

=ARRAYFORMULA(IF(LEN(A1:A), TRANSPOSE(SPLIT(QUERY(TRANSPOSE(SPLIT(A1:A, “,”))&" “&B1:B&” "&C1:C, “SELECT * WHERE Col1 IS NOT NULL”), " ")), “”))

This formula splits the years, combines them with the make and model, and presents each combination as a separate entry in column D. It also handles blank cells gracefully.

Remember to drag the formula down or double-click the fill handle to apply it to all rows. Let me know if you need any clarification on how this works.