Combination

Split a pasted list into a clean column

Split a list of invoice numbers pasted into one cell on the commas, strip the stray spaces with TRIM and stack the pieces into one column with TOCOL, ready for matching.

Functions used:TEXTSPLITTRIMTOCOLExcel 2024 or later

Difficulty rating

Worked example

Invoice numbers pasted into one cell

Scenario

For the receivables audit of Veldhuis Installatie BV, the client emails you the invoices that were paid after year end 31 December 2025, pasted into one cell with irregular spaces around the commas.

You want one clean column of invoice numbers so you can match each one against the receivables list.

The data

ABCD
1Invoice numbers (as pasted)
225-0412, 25-0415 ,25-0420,25-0431 , 25-043725-0412=TOCOL(TRIM(TEXTSPLIT(A2, ",")))=TOCOL(TRIM(TEXTSPLIT(A2; ",")))
325-0415
425-0420
525-0431
625-0437
7
Formula cell (C2)Spilled resultMarks: extra space

Formula in C2

=TOCOL(TRIM(TEXTSPLIT(A2, ",")))
=TOCOL(TRIM(TEXTSPLIT(A2; ",")))

Result: 25-0412 / 25-0415 / 25-0420 / 25-0431 / 25-043725-0412 / 25-0415 / 25-0420 / 25-0431 / 25-0437

How the pieces fit together

Watch out: TRIM only removes normal spaces. A non-breaking space, CHAR(160), stays, and the lengths stay too long.

The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.

  1. =TEXTSPLIT(A2, ",")
    =TEXTSPLIT(A2; ",")

    Cuts the text in A2 at every comma and spreads the pieces over one row: 5 cells holding "25-0412", " 25-0415 ", "25-0420", " 25-0431 " and " 25-0437". The spaces around the numbers are still there.

  2. =LEN(TEXTSPLIT(A2, ","))
    =LEN(TEXTSPLIT(A2; ","))

    Counts the characters of each piece: 7, 9, 7, 10 and 8. A clean invoice number is 7 characters long, so the extra 2, 3 and 1 characters are the spaces that would break a match.

  3. =TRIM(TEXTSPLIT(A2, ","))
    =TRIM(TEXTSPLIT(A2; ","))

    TRIM works on every cell of the row at once and removes the spaces at the start and end. All five pieces are now 7 characters: 25-0412, 25-0415, 25-0420, 25-0431 and 25-0437.

  4. =TOCOL(TRIM(TEXTSPLIT(A2, ",")))
    =TOCOL(TRIM(TEXTSPLIT(A2; ",")))

    TOCOL turns the row of 5 into a column of 5, from C2 down to C6. This column can be used directly as the lookup values of XLOOKUP or as the criteria of a COUNTIF.

Step by step

  1. TEXTSPLIT(A2, ",") cuts the text at its 4 commas, which gives 5 pieces in one row: "25-0412", " 25-0415 ", "25-0420", " 25-0431 " and " 25-0437".
  2. The lengths of the pieces are 7, 9, 7, 10 and 8 characters. Only the first and third are clean.
  3. TRIM removes the spaces at the start and end of each piece: 9 - 2 = 7, 10 - 3 = 7 and 8 - 1 = 7. All five are now 7 characters.
  4. TOCOL stacks the row of 5 into a column of 5. It spills from C2 down to C6: 25-0412, 25-0415, 25-0420, 25-0431 and 25-0437.

Why it works

TEXTSPLIT returns an array, and TRIM accepts an array and cleans every element, so you do not need a helper column per piece.

TOCOL then changes the shape from a row to a column, which is the shape that lookups and COUNTIF criteria expect. The whole result is one spilled formula that updates when the pasted text changes.

Watch out for

  • A trailing comma ("25-0437,") creates an empty last piece, and you get a blank row at the bottom of the column. Set the fourth argument of TEXTSPLIT to TRUE to skip empty pieces.
  • Text copied from a PDF or a web page often holds non-breaking spaces, CHAR(160). Replace them first with SUBSTITUTE(A2, CHAR(160), " ").

  • If the client separates the numbers with semicolons or line breaks as well, a single comma misses them and you get one long piece. Give TEXTSPLIT a list of delimiters, for example {",", ";"}.

    A line break is CHAR(10) and must be in the delimiter list too.

Variations

=TRIM(TEXTSPLIT(A2, , ","))
=TRIM(TEXTSPLIT(A2; ; ","))

Put the comma in the third argument (the row delimiter) instead of the second. TEXTSPLIT then returns a column directly and you do not need TOCOL. Same five numbers.

=TOCOL(TRIM(TEXTSPLIT(A2, ",", , TRUE)))
=TOCOL(TRIM(TEXTSPLIT(A2; ","; ; TRUE)))

The same formula with the fourth argument TRUE, so a trailing comma or two commas in a row ("25-0415,,25-0420") do not leave blank rows. Use this when the client's list is not predictable.

=TRIM(MID(SUBSTITUTE(A2, ",", REPT(" ", 100)), SEQUENCE(5, , 0) * 100 + 1, 100))
=TRIM(MID(SUBSTITUTE(A2; ","; REPT(" "; 100)); SEQUENCE(5; ; 0) * 100 + 1; 100))

The older way without TEXTSPLIT: replace every comma by 100 spaces, cut out windows of 100 characters starting at 1, 101, 201, 301 and 401, and trim each window. It gives the same five numbers.

It has two limits: you state the number of pieces (5) yourself, and the pieces together, without the commas, must not be longer than 100 characters. With a longer list a piece is cut off or missing, without an error.

All combinations