# Formula or Macro to split up a number string

#### Angela N2 Publishing

##### New Member
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=REPLACE(REPLACE(REPLACE(REPLACE(IF(LEN(A2)=31,REPLACE(REPLACE(A2,30,0," "),26,0,"-"),A2),21,0," "),15,0," "),6,0," "),3,0," ")``

#### Angela N2 Publishing

##### New Member
Thank you! Almost got it!

 "1 027 190000024 687094 843606241045"

#### Fluff

##### MrExcel MVP, Moderator
Does your data actually include the quotes?
If so do you want them kept, or removed?

#### Angela N2 Publishing

##### New Member

I receive the data with the quotes, but do not need to keep them

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=REPLACE(REPLACE(REPLACE(REPLACE(IF(LEN(A2)=33,REPLACE(REPLACE(SUBSTITUTE(A2,CHAR(34),""),30,0," "),26,0,"-"),SUBSTITUTE(A2,CHAR(34),"")),21,0," "),15,0," "),6,0," "),3,0," ")``

#### Angela N2 Publishing

##### New Member

PERFECT!

 "1027190000024687094743606241049" 10 271 900000246 870947 43606-2410 49 "1027190000024687094843606241045" 10 271 900000246 870948 43606-2410 45 "1027190000024687095043606241029" 10 271 900000246 870950 43606-2410 29 "1027190000024687095143606241025" 10 271 900000246 870951 43606-2410 25 "1027190000024687095243606241019" 10 271 900000246 870952 43606-2410 19 "1027190000024687095343606241220" 10 271 900000246 870953 43606-2412 20 "1027190000024687095443606241228" 10 271 900000246 870954 43606-2412 28 "1027190000024687095543606241236" 10 271 900000246 870955 43606-2412 36 "1027190000024687095643606241402" 10 271 900000246 870956 43606-2414 02 "1027190000024687095743606241414" 10 271 900000246 870957 43606-2414 14 "1027190000024687165843615230240" 10 271 900000246 871658 43615-2302 40

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

#### Rick Rothstein

##### MrExcel MVP
Since the TEXT function will track up to 15 full digits (I think that is the limit), you can use this shorter formula as well...
Excel Formula:
``=TEXT(MID(A2,2,14),"00 000 000000000 ")&TEXT(MID(A2,16,11),"000000 00000")&IF(LEN(A2)>27,TEXT(MID(A2,27,6),"-0000 00"),"")``

#### Eric W

##### MrExcel MVP
If the TEXTJOIN function is available, this is even shorter:

Excel Formula:
``=TEXTJOIN(" ",1,MID(A1,{2,4,7,16,22},{2,3,9,6,5}))&IF(LEN(A1)=33,"-"&MID(A1,27,4)&" "&MID(A1,31,2),"")``

1,128,072
Messages
5,628,476
Members
416,320
Latest member
Insightful111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back