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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,749
Office Version
  1. 365
Platform
  1. Windows
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," ")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,749
Office Version
  1. 365
Platform
  1. Windows
Does your data actually include the quotes?
If so do you want them kept, or removed?
 
Joined
Jul 31, 2019
Messages
14

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,749
Office Version
  1. 365
Platform
  1. Windows
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," ")
 
Joined
Jul 31, 2019
Messages
14

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
55,749
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,891
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,776
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),"")
 

Watch MrExcel Video

Forum statistics

Threads
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.
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