Split a cell into 2 columns

[Chris L]

I have a situation where I input a lot of information into a table to generate certifications and invoices for material I process.
There are a lot of typo errors from manual inputting.
The customer sent me this .xls file that he said he can send every time he sends product.
At the very least I need a way to separate the information in column D into two separate columns, one with P/N and one with J/N.

My ultimate goal would be to keep sheet1 and generate a separate sheet for each PO (Tab name = PO?)that only has a column for P/N, J/N, and QTY,

 

[Marc L]

Select a range to 'split' then use the Excel convert feature Text To Columns within Data menu …​

​

 

[Chris L]

Marc, I can't get that to give me what I need.
cell D2 if I use the text to columns at best gives me these three columns.
P/N: WT4-928095J/N: 91084900-001

If I use delimited and use J as the delimiter I can strip the J/N: part out and am left with the P/N:
but how can I strip out everything to the left of the J?

 

[Marc L]

As I get 2 columns only with Text To Columns : just using the Line Feed character as delimiter, entry via combo keys Alt 010 …​

​

 

[pecoflyer]

Power Query does the job

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"PO #", "Order #(lf)Qty", "PO#(lf) Due Date"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Part Number #(lf)and Job Number", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Part Number #(lf)and Job Number.1", "Part Number #(lf)and Job Number.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([#"Part Number #(lf)and Job Number.1"] <> null and [#"Part Number #(lf)and Job Number.1"] <> "Part Number "))
in
    #"Filtered Rows"

 

[Peter Bartholomew]

Using an up to date version of Excel 365

= REGEXEXTRACT(numbers, "[\w\-]{5,20}" & {"","$"})

 

[AliGW]

With Excel 2024 or 365:

=TEXTSPLIT(D2,CHAR(10))

 

[AliGW]

SPILL alternative:

=SUBSTITUTE(DROP(IFERROR(REDUCE("",D1:D188,LAMBDA(x,y,VSTACK(x,IFERROR(TEXTSPLIT(y,CHAR(10)),"")))),""),1),"and ","")

 

[Peter Bartholomew]

@AliGW
Good to see you pushing the boundaries of modern Excel. You might like to try my variant on the built-in MAP function.

A version of Excel MAP helper function that will return an array of arrays

A version of Excel MAP helper function that will return an array of arrays - MAPλ.txt

gist.github.com

Don't bother about how it works at first. It is meant to be used as a straightforward alternative to the Microsoft function, having an identical parameter signature, but one that is able to return a two dimensional array of two dimensional arrays. The MAPλ function does call a second Lambda function EVALTHUNKARRλ that can be reused with other helper functions if you so choose.

 

[AliGW]

I'd hardly call it 'pushing the boundaries'! It's nothing compared to what some people on other forums are doing with it, @Peter Bartholomew, but I am enjoying developing my use of it at my own pace. Thanks for the link.