Forum Discussion

LV0510's avatar
LV0510
Copper Contributor
Jan 11, 2019

data in 1 cell . Split into 4 cells

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

 

Ex.  Split 24001001000100

into this:  24   001   0010   00100

excel

3 Replies

  • LV0510's avatar
    LV0510
    Copper Contributor
    Jan 11, 2019

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

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Jan 11, 2019

      Hi,

       

      Please try this formula:

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

       

      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

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Jan 11, 2019

      Hello

       

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