Help splitting order numbers by character for reconciliation

Hi all, In all my years of Excel use, I've never come across such a stupid human-made issue. I currently need to get an orders report from a supplier and reconcile them, however, they have decid...

excel

Excel for web

Formulas and Functions

Lorenzo

Feb 02, 2022

Hi GrandMoffReknaw

Assuming data stored in tblOrders:

in F4

=IF(ISNUMBER(SEARCH(" & ",[@[Order Number]])),
    LET(
      Delim,  "|",
      Value,  SUBSTITUTE([@[Order Number]], " & ", Delim),
      seqLen, SEQUENCE( LEN(Value)+1 ),
      arrStart,
        LET(
          Dvalue, Delim & Value,
          split,  MID(Dvalue, seqLen, 1),
          arr,    (split = Delim) * seqLen,
          FILTER(arr, arr > 0)
        ),
      arrEnd,
        LET(
          valueD, Value & Delim,
          split,  MID(valueD, seqLen, 1),
          arr,    (split = Delim) * seqLen,
          FILTER(arr, arr > 0)
        ),
      arrValues,  MID(Value, arrStart, arrEnd-arrStart),
      valueOccur, COUNTIF(
                OFFSET(tblOrders[[#Headers],[Order Number]],1,0,(ROW()-ROW(tblOrders[#Headers]))),
                [@[Order Number]]
            ),
      INDEX(arrValues, valueOccur)
    ),
    [@[Order Number]]
)

Corresponding sample attached (tested in Excel Web)

XL365_SplitOrders.xlsx20 KB

=IF(ISNUMBER(SEARCH(" & ",[@[Order Number]])), LET( Delim, "|", Value, SUBSTITUTE([@[Order Number]], " & ", Delim), seqLen, SEQUENCE( LEN(Value)+1 ), arrStart, LET( Dvalue, Delim & Value, split, MID(Dvalue, seqLen, 1), arr, (split = Delim) * seqLen, FILTER(arr, arr > 0) ), arrEnd, LET( valueD, Value & Delim, split, MID(valueD, seqLen, 1), arr, (split = Delim) * seqLen, FILTER(arr, arr > 0) ), arrValues, MID(Value, arrStart, arrEnd-arrStart), valueOccur, COUNTIF( OFFSET(tblOrders[[#Headers],[Order Number]],1,0,(ROW()-ROW(tblOrders[#Headers]))), [@[Order Number]] ), INDEX(arrValues, valueOccur) ), [@[Order Number]] )

Forum Discussion

Help splitting order numbers by character for reconciliation

Resources