You have downloaded data from accounting system. The data includes posting year, posting month, account and amount.

year;month;account;amount

2025;1;1000;167

2025;1;1010;640

2025;1;2000;15

2025;1;2000;965

2025;1;2010;278

2025;2;2010;738

2025;2;2010;973

2025;2;2010;108

2025;2;1000;947

The data should be presented in a reportable format.

That can be done via data – text to columns.

Now the data is in countable format.

The same thing can be done with TEXTSPLIT. The function consists of only two mandatory arguments, the cell and the separator. Other arguments are not mandatory.

Copy the function downwards.

The function splits the string, but TEXTSPLIT splits texts and considers the values as text. Therefore, the cells are aligned in left

The TEXTSPLIT can be framed with NUMBERVALUE, which returns to a numeric value. The first row is text and that does not understand the NUMBERVALUE function. The cells are aligned in right, the values are numbers.

The formula text in F11 is: =NUMBERVALUE((TEXTSPLIT(B11;”;”)))

One way to distinguish whether line is text or number is to take four first digits and set that as numeric value. Then we test that with ISNUMBER function. If the function returns TRUE, then the row is numeric data. If the function returns FALSE, then the data is textual data.

 Therefore, we have to start with IF-function, which would check whether line is numeric data and NUMBERVALUE function or if the line is text data and NUMBERVALUE is not needed.

Now only one sentence can tackle both text and number values. The first row is text and other rows consists of number values.

The sentence in E2 is:

=IF(ISNUMBER(NUMBERVALUE(LEFT(B2;4)));(NUMBERVALUE(TEXTSPLIT(B2;”;”)));(TEXTSPLIT(B2;”;”)))

To sum up, the text to columns is still a very handy way to separate a string with separators into multiple cells. As we saw, it is possible to tackle also with TEXTSPLIT, but the sentence got quite long. If you want to keep the source data and reportable data separately, then TEXTSPLIT is a possible solution. Text to columns is separating source data to cells and the original source data is not available any more.