data in 1 cell . Split into 4 cells

Copper Contributor

 I need to split a 14 digit number (saved as text) into 4 different columns

 

Ex.  Split 24001001000100

into this:  24   001   0010   00100

3 Replies

I can do this using the text to columns function however, it removes the 0's and I need those

Hello

 

You did not set the data format "Text" for the four columns in step 3.

 

Text%20Import%20Wizard%20Step%203%20of%203

 

Hi,

 

Please try this formula:

=MID($A$1,CHOOSE(COLUMN(A1),1,3,6,10),COLUMN(A1)+1)

2019-01-11_23-48-08.png

 

Let's say the number is in cell A1 as the screenshot above, then copy the formula in cell B1 and drag it to the right.

 

Hope that helps