How to split a string by certain length in excel and put it into columns

In excel 2016 I want to split a single string with no separators into a 2-character cell under column A (same column). The string has no fixed length.

Example AAB4101X -> AA | B4 | 10 | 1X Example single string in the cell AAB4101X ->

Desired column: AA B4 10 1X

Additional question: Will the formula work with Japanese characters?

Example:戦略聞く中国　-> Desired column: 戦略 聞く 中国

I would appreciate any suggestion as well.

3 Answers

::Caveat::

Below show method addresses only the first part of the question. So readers please do not consider this for the other text is in Japanese language.

Since I'm unable to reproduce the text in Japanese language, therefore I've skipped it.

How it works:

• Put helper data are in Red, in range C136:G136

• If the string's length is 8 only, then you may skip Helper Data in Cell G136.

• For neatness later on you may hide Helper data.

• Formula in cell C137 ( fill it across ) :

  =MID($A137,COLUMN()+C$136-COLUMN($C137)+1,2)

N.B.

• You may adjust the cell references in the formula as needed.

Presenting a generalised solution for any string and any Split Length.------

STEP 1 : Give titles in Row 1 : A1 = “String”, B1 =”Length”, C1 = “Split Length”, D1=1, E1= D1+1,... Copy the formula of E1, right across ------ So as to get Series : 1, 2, 3, 4,5,....D1 Onwards****************

STEP 2 : First Third row, Enter the given String say “ABCDEFGHIJKLMNOPQ” in A3; In B3 write formula B3 = LEN(A3),..it will be giving length of string, here 17, ......, In C3 enter the desired Split Length; in our ex. It is 2 ; So enter value 2 in C3

STEP 3 : Now in Second Row ; write formula in D2, = $C$3D1, after this Copy the formula of Cell D2, right across. In our case you will get 2,4,6,8..***

STEP 4 : Formula in Cell D3 = LEFT($A$3,C3)-----------to get “AB” ****

STEP 5 : Enter Formula in E3 = MID($A$3,D2+1,$C$3)----------***** STEP 6 : Copy the formula of E3, right across in 3rd row.....to get desired result. As “AB”, “CD”, “EF” ..... etc.

Note : Now change the split length; say 3. So enter 3 in Cell C3 the results would be : “ABC”, “DEF”, “GHI” etc. Like that Enter 1 to get A, B, C, D..... Enter 4 in C3 to get : ABCD, EFGH, IJKL...etc.****** Any string, any split length will work.

Thanks for giving the opportunity to think.

Previous answer can be improved to eliminate need for helper data by using column relationships to calculate starting point for MID function.

Original formula: =MID($A137,COLUMN()+C$136-COLUMN($C137)+1,2) the C$136 was where the helper data is stored.

Better formula: =MID($A137,(COLUMN() - COLUMN($C137)*2 + 1,2)