Cell to matched columns

Question
1

I have a files in which a single column’s record contains lots of data in it, which can I’d like to extract to columns that match the data as organized in the cell. The column headers UPC|SKU|Category|Color|Size|Origin already exist (all blank/empty). The cells for each record don’t follow the same content/order for all, so there are approximately 300 columns the data could go to.

The data in a cell is structured (with line breaks) as:

CELL B2

UPC:12345678

SKU:ABC123

Category:ABCDE

Name:Data

CELL B3

SKU:ABC567

Size:1/2"

Color:Purple

CELL B4

SKU:ABC910

Weight:5lbs

Origin:China

2

So it is in Excel and looks like this?

image
image606×664 31.7 KB

3

If so you can do it with some careful use of Split Col and Spread transforms.

image
image1206×138 37 KB

image
image1162×406 42.1 KB

njc.transform (4.0 KB)

I can’t post the input .xlsx here.

1 Like
4

If you need the columns in a particular order you can do that using a Stack transform with the desired header. See:

5

I see what you’re going for, as I had tried the split col and spread functions previously, but it’s not complying with me. The line breaks don’t seem to respond to regex the correct manner.

6

Make sure that simplify whitespace is not checked for the input, as that will convert the carriage returns in the cells to spaces.

To check the whitespace characters in a cell, see:

I have emailed you a .transform that splits your input correctly.