Numbers with spaces in them

%3CLINGO-SUB%20id%3D%22lingo-sub-1768215%22%20slang%3D%22en-US%22%3ENumbers%20with%20spaces%20in%20them%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768215%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20to%20create%20a%20sheet%20with%20consecutive15%20digit%20numbers.%20When%20I%20enter%20the%2015%20digit%20number%20with%20spaces%20between%20every%20third%20number%20excel%20will%20not%20let%20me%20create%20a%20list%20with%20consecutive%20numbers.%20If%26nbsp%3B%20I%20remove%20the%20spaces%20it%20works.%20Is%20there%20a%20way%20to%20make%20it%20recognize%20the%20spaces%3F%20or%20should%20I%20create%20the%20list%20with%20out%20spaces%20and%20then%20is%20there%20a%20way%20to%20add%20the%20spaces%20to%20the%20entire%20column%20after%20it%20has%20been%20created%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20Example%20of%20number%26nbsp%3B%20124%20000%20199%20264%20100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1768215%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768291%22%20slang%3D%22en-US%22%3ERe%3A%20Numbers%20with%20spaces%20in%20them%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768291%22%20slang%3D%22en-US%22%3EYou%20could%20create%20the%20list%20w%2Fo%20spaces%20and%20then%20put%20the%20spaces%20in%20using%20a%20formula%20(assuming%20all%20of%20the%20numbers%20are%2015%20digit).%3CBR%20%2F%3E%3CBR%20%2F%3E%3DTEXTJOIN(%22%20%22%2C%2CMID(A1%2C%7B1%2C4%2C7%2C10%2C13%7D%2C3))%3CBR%20%2F%3E%3CBR%20%2F%3EThen%2C%20copy%2Fpaste%20special%20value%20to%20hardcode%20the%20formula%20results%20and%20you%20could%20get%20rid%20of%20the%20numbers%20w%2Fo%20spaces.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have to create a sheet with consecutive15 digit numbers. When I enter the 15 digit number with spaces between every third number excel will not let me create a list with consecutive numbers. If  I remove the spaces it works. Is there a way to make it recognize the spaces? or should I create the list with out spaces and then is there a way to add the spaces to the entire column after it has been created?

 

  Example of number  124 000 199 264 100

2 Replies
Highlighted
You could create the list w/o spaces and then put the spaces in using a formula (assuming all of the numbers are 15 digit).

=TEXTJOIN(" ",,MID(A1,{1,4,7,10,13},3))

Then, copy/paste special value to hardcode the formula results and you could get rid of the numbers w/o spaces.
Highlighted

@ChristyQuaedvlieg If adding the spaces is a mere cosmetic issue, you may custom format the 15-digit number as 000 000 000 000 000.

It will remain a number but just displayed with spaces between every group of three digits.

Screenshot 2020-10-11 at 09.58.02.png