Thanks for the very quick response, I was able to use your method of concatenation but used the Keep Empty Values setting to preserve the empty cells in the middle of the data. My example didn’t include this but the live data I am working on has lots of empty cells.
This posed a problem that keeping the empty values also kept the data that I was trying to remove at the start of the lines. I used the Replace node after the Concat Cells to remove the leading and trailing vertical bars with Regex before it ran the Split Col and this worked for me.
The Regex looks like this, the ^ is the anchor for the start of the line and the $ is the end of line
A regex that could be applied per row on import would work, in this case I just need to remove the leading whitespace which would be the same as a shift left.
In BBEdit I can select all of the text and shift left several times, it will keep going until it meets up with a character, this will not affect lines that start with a character other than white space.
It’s difficult after the import since you have already constructed columns and I can’t process the row without concatenation.
A per row regex could be tricky as I am not sure how you could handle text that spanned multiple cells.
A transform that moved everything left (or up, right or down) until all empty cells were filled would be relatively straightforward to add. I’m not sure what we would call it though. Shuffle and Fill are already taken.
Admittedly a per row regex would only work with text files, but the ability to affect the input before it becomes a row of cells would be useful, it would save me taking the data through BBEdit.
Until today, most of the data wrangling I have done is a mix of FileMaker and BBEdit with GREP, it’s a powerful combination but I can see an easier route with EDT, it’s a great tool.
I’m not sure about multiple cells either, but given that you would probably have to craft the regex to fit the data anyway, you would probably take that into account. I’ve done a lot with tabular data especially swapping columns or names like first with last etc.
Thanks, that’s a really good solution, I like the use of Stack to put the header on the data. I also tried concatenating just the first two columns and using Reorder Cols to put them at the start but it didn’t work as expected.
I also experimented with Header, that works well if you import without a header so the header is part of the data and then use Header to promote the first row.