Formula or Macro to split up a number string

[Angela N2 Publishing]

I know this has got to be an easy request, so I'm putting in out there to you all for help! I have a string of numbers that I want to split up, either with spaces in between or into separate columns.

The number string is always going to be either 31 digits or 25 digits, like this:

2027190000024686172148322451725 = 31 digits
0031190000024672904639206 = 25 digits

And I want to split them up like this:
20 271 900000246 861721 48322-4517 25
00 311 900000246 729046 39206


2 digits ... then 3 digits .... then 9 digits ... then 6 digits ... then 5 digits with a hyphen and 4 more digits ... then 2 digits

And if the hyphen part is too difficult, then the 5 digits can be separate, with space in between the next 4 digits or separated into columns, instead of a hyphen

For the 25 digits, this would just stop after the 5 digits (no hyphen or more numbers)

Like These:

"1027190000024687165843615230240"	10 271 900000246 871658 43615-2302 40
"1027190000024687166243615230321"	10 271 900000246 871662 43615-2303 21
"1027190000024687169243615226238"	10 271 900000246 871692 43615-2262 38
"1027190000024687169343615227750"	10 271 900000246 871693 43615-2277 50
"1027190000024687169443615227764"	10 271 900000246 871694 43615-2277 64
"2027190000024686159448374181670"	20 271 900000246 861594 48374-1816 70
"2027190000024686159648374181670"	20 271 900000246 861596 48374-1816 70
"2027190000024686159748374121820"	20 271 900000246 861597 48374-1218 20
"2027190000024686159848374121820"	20 271 900000246 861598 48374-1218 20
"2027190000024686165148084166599"	20 271 900000246 861651 48084-1665 99
"2027190000024686165448084530580"	20 271 900000246 861654 48084-5305 80

 

[Peter_SSs]

@Angela N2 Publishing
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If the TEXTJOIN function is available, this is even shorter:

Or a tiny bit shorter again

20 10 06.xlsm
	A	B
1
2	"1027190000024687094843606241045"	10 271 900000246 870948 43606-2410 45
3	"1027190000024687095043606241029"	10 271 900000246 870950 43606-2410 29
4	"1027190000024687095743606241414"	10 271 900000246 870957 43606-2414 14
5	"1027190000024687165843615230240"	10 271 900000246 871658 43615-2302 40
6	"0031190000024672904639206"	00 311 900000246 729046 39206
Reformat

Cell Formulas
Range	Formula
B2:B6	B2	=SUBSTITUTE(TEXTJOIN(" ",1,MID(LEFT(A2,LEN(A2)-1),{2,4,7,16,22,27,31},{2,3,9,6,5,4,2}))," ","-",5)