Formula or Macro to split up a number string

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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
How about
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," ")
 
Upvote 0
Does your data actually include the quotes?
If so do you want them kept, or removed?
 
Upvote 0
In that case, how about
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," ")
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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"),"")
 
Upvote 0
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),"")
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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
Back
Top