functiongoogle-sheetsautomationsku

Google Sheets - Create SKU that automates content into separate columns


I would like to create a column (SKU) in my Google sheets CSV that when data is manually input in each cell, it will automatically fill out the content in 3 other columns (Title, Colour, Size).

I have created an example CSV with the necessary columns highlighted.

Column B - Product title Column I - Colour Column K - Size Column N - SKU

Example SKU Inputs (Manually entered into column N)

L24-1-000 C-2-150

Breakdown of values assigned to each section:

L24 = Lightweight trainers S24 = Sandals

1 = Blue 2 = Red

000 = 0 150 = 1.5

Output (Automated in columns B, I and K)

'Lightweight trainers', 'Blue', '0'

'Sandals', 'Red', '1.5'

Link to CSV

https://docs.google.com/spreadsheets/d/1E6NypRcWk4kR7WGsyEzt7Uuja8dU7SaszT9lvJPpHcg/edit?usp=sharing


Solution

  • Please try this:

    B1: =arrayformula(IF(A1:A="Handle","Title",IF(INDEX(SPLIT(N1:N, "-"),,1)="L32","Lightweight Trainers", IF(INDEX(SPLIT(N1:N, "-"),,1)="S24","Sandals", "Boots"))))

    I1: =arrayformula(IF(A1:A="Handle","Option1 Value",IF(LEN(INDEX(SPLIT(N1:N, "-"),,2))>1,"Multicoloured",IF(INDEX(SPLIT(N1:N, "-"),,2)=1,"Blue",IF(INDEX(SPLIT(N1:N, "-"),,2)=2,"Red",IF(INDEX(SPLIT(N1:N, "-"),,2)="C","Cream",))))))

    K1: =arrayformula(IF(A1:A="Handle","Option2 Value",MID(N1:N,LEN(N1:N) - 2,3)))