Formula or Macro to split up a number string

Joined
Jul 31, 2019
Messages
14
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
@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
AB
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
RangeFormula
B2:B6B2=SUBSTITUTE(TEXTJOIN(" ",1,MID(LEFT(A2,LEN(A2)-1),{2,4,7,16,22,27,31},{2,3,9,6,5,4,2}))," ","-",5)
 
Last edited:

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,127,581
Messages
5,625,612
Members
416,124
Latest member
DeMoNloK

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top