Convert space-delimited data to columns

I have a software package that requires serial number effectively data to be entered in a particular format.

As this can cover hundreds of lines I would like to make it less tedious to enter, and as my MACRO knowledge is very basic I would appreciate some help.

The data starts off in format below in example 1. After the data is CUT from the .html or .pdf document and PASTE into EXCEL. I would like the MACRO to start by pressing an activate button within EXCEL,

The serial numbers always have four digits with single serial numbers being separated by spaces and ranges being separated by a hyphen with the odd carriage return depending on how many numbers there are.

I would like the data to end up in two separate columns as shown in example 2.


Example 1 (Starting format)
* indicates space
- indicates a range, this needs to be separated into two separate columns

2252*2254*2256*2257*2259*2272*2274-2276*2278*2280*2282*2284*2286-2641*2643-2681*2683-2712*2714-2717*2719*2721*2724*2726*2727*2729* 2733*2735 *2738*2739*2746

Example 2 (Finished format ready to be paste into software package
2252
2254
2256
2257
2259
2272
2274 2276
2278
2280
2282
2284
2286 2641
2643 2681
2683 2712
2714 2717
2719
2721
2724
2726
2727
2729
2733
2735
2738
2739
2746

Many Thanks in advance for any help you can give.

Oooops sorry, as more than 2 hours have passed please can you change the title to:

How do I convert space separated data into columns using Visual Basics in EXCEL 2003

This works well. Many thanks for your help.

I’m slowly learning the power of MACRO!

I have adjusted the script to include a command button and remove the carriage returns, which works fine (see below) for small data strings but when I paste more than 911 characters, it no longer captures every carriage return.

Is there a limit of 911 characters in string format?

Private Sub CommandButton1_Click()
ActiveSheet.Select
Range("A1").Select

With ActiveCell
' For i = 1 To 5
ActiveCell.Replace What:=Chr(9), Replacement:=" "
ActiveCell.Replace What:=Chr(10), Replacement:=" "
ActiveCell.Replace What:=Chr(13), Replacement:=" "
' Next i
End With
ConvertData

Sub ConvertData()
Dim i As Long
Dim as1() As String
Dim as2() As String

as1 = Split(WorksheetFunction.Trim(Selection(1).Text), " ")
For i = 0 To UBound(as1)
as2 = Split(as1(i), "-")
Selection.Offset(i + 1).Resize(, UBound(as2) + 1).Value = as2
Next i
End Sub

Works OK with this data string:

0497 0500 0501 0503 0504 0506-0508 0510-0512 0523 0525 0527 0528 0530 0531
0534 0537 0539 0540 0542 0543 0549 0551 0554 0556 0558 0561 0565 0568 0569
0571 0573 0575 0579 0580 0582 0584 0587 0589 0592 0613 0615 0622 0638 0640
0645 0655 0658 0659 0661 0662 0667 0669 0671 0676 0678 0683 0685 0696 0698
0702 0704 0705 0709 0710 0712 0718 0720 0722 0726 0733 0739 0741 0743 0747
0751 0756 0758 0760 0762 0766 0770 0774 0778 0780 0784 0786 0791 0793 0795
0801 0803 0805 0807 0809 0812 0814 0816 0818 0820 0822 0824 0826 0830 0832
0834 0836 0839 0842 0846 0849 0851 0856 0857 0859 0865 0866 0872 0874 0877
0881 0884 0886 0892 0894 0895 0899 0900 0902 0903 0905 0907 0911 0912 0916
0918 0919 0923 0927 0928 0930 0932 0934 0937 0943 0950 0953 0955 0957 0958
0962 0964 0966 0969 0971 0975 0977 0981 0988 0990 0996 1001 1003 1007 1011
1013 1014 1032 1035 1037 1039 1050 1057 1075 1076 1079 1083 1085 1104 1105
1110 1117

But not with this one:

0497 0500 0501 0503 0504 0506-0508 0510-0512 0523 0525 0527 0528 0530 0531
0534 0537 0539 0540 0542 0543 0549 0551 0554 0556 0558 0561 0565 0568 0569
0571 0573 0575 0579 0580 0582 0584 0587 0589 0592 0613 0615 0622 0638 0640
0645 0655 0658 0659 0661 0662 0667 0669 0671 0676 0678 0683 0685 0696 0698
0702 0704 0705 0709 0710 0712 0718 0720 0722 0726 0733 0739 0741 0743 0747
0751 0756 0758 0760 0762 0766 0770 0774 0778 0780 0784 0786 0791 0793 0795
0801 0803 0805 0807 0809 0812 0814 0816 0818 0820 0822 0824 0826 0830 0832
0834 0836 0839 0842 0846 0849 0851 0856 0857 0859 0865 0866 0872 0874 0877
0881 0884 0886 0892 0894 0895 0899 0900 0902 0903 0905 0907 0911 0912 0916
0918 0919 0923 0927 0928 0930 0932 0934 0937 0943 0950 0953 0955 0957 0958
0962 0964 0966 0969 0971 0975 0977 0981 0988 0990 0996 1001 1003 1007 1011
1013 1014 1032 1035 1037 1039 1050 1057 1075 1076 1079 1083 1085 1104 1105
1110 1117 1123 1128 1143 1146 1156 1158 1163 1166 1173 1177 1183 1187 1192
1194 1196 1215 1234 1235 1240 1246 1248 1251 1253 1257 1266 1270 1272 1274
1280 1282 1284 1290 1300 1302 1304 1327 1332 1334 1337 1339 1341 1343 1349
1351 1353 1355 1359 1363 1365 1367 1368 1374 1376 1381 1383 1398 1400 1402
1407 1409 1411 1416 1418 1419 1422 1424 1427 1432 1435 1437 1446 1452 1459

Please can you help me adjust the script the capture all the carriage returns and convert all the data.

Many Thanks,

Re-listed for compliance with forum rule 3.

I have adjusted the script to include a command button and remove the carriage returns, which works fine (see below) for small data strings but when I paste more than 911 characters it no longer captures every carriage return.

Is there a limit of 911 characters in string format?

Please Login or Register  to view this content.

Works OK with this data string:

0497 0500 0501 0503 0504 0506-0508 0510-0512 0523 0525 0527 0528 0530 0531
0534 0537 0539 0540 0542 0543 0549 0551 0554 0556 0558 0561 0565 0568 0569
0571 0573 0575 0579 0580 0582 0584 0587 0589 0592 0613 0615 0622 0638 0640
0645 0655 0658 0659 0661 0662 0667 0669 0671 0676 0678 0683 0685 0696 0698
0702 0704 0705 0709 0710 0712 0718 0720 0722 0726 0733 0739 0741 0743 0747
0751 0756 0758 0760 0762 0766 0770 0774 0778 0780 0784 0786 0791 0793 0795
0801 0803 0805 0807 0809 0812 0814 0816 0818 0820 0822 0824 0826 0830 0832
0834 0836 0839 0842 0846 0849 0851 0856 0857 0859 0865 0866 0872 0874 0877
0881 0884 0886 0892 0894 0895 0899 0900 0902 0903 0905 0907 0911 0912 0916
0918 0919 0923 0927 0928 0930 0932 0934 0937 0943 0950 0953 0955 0957 0958
0962 0964 0966 0969 0971 0975 0977 0981 0988 0990 0996 1001 1003 1007 1011
1013 1014 1032 1035 1037 1039 1050 1057 1075 1076 1079 1083 1085 1104 1105
1110 1117

But not with this one:

0497 0500 0501 0503 0504 0506-0508 0510-0512 0523 0525 0527 0528 0530 0531
0534 0537 0539 0540 0542 0543 0549 0551 0554 0556 0558 0561 0565 0568 0569
0571 0573 0575 0579 0580 0582 0584 0587 0589 0592 0613 0615 0622 0638 0640
0645 0655 0658 0659 0661 0662 0667 0669 0671 0676 0678 0683 0685 0696 0698
0702 0704 0705 0709 0710 0712 0718 0720 0722 0726 0733 0739 0741 0743 0747
0751 0756 0758 0760 0762 0766 0770 0774 0778 0780 0784 0786 0791 0793 0795
0801 0803 0805 0807 0809 0812 0814 0816 0818 0820 0822 0824 0826 0830 0832
0834 0836 0839 0842 0846 0849 0851 0856 0857 0859 0865 0866 0872 0874 0877
0881 0884 0886 0892 0894 0895 0899 0900 0902 0903 0905 0907 0911 0912 0916
0918 0919 0923 0927 0928 0930 0932 0934 0937 0943 0950 0953 0955 0957 0958
0962 0964 0966 0969 0971 0975 0977 0981 0988 0990 0996 1001 1003 1007 1011
1013 1014 1032 1035 1037 1039 1050 1057 1075 1076 1079 1083 1085 1104 1105
1110 1117 1123 1128 1143 1146 1156 1158 1163 1166 1173 1177 1183 1187 1192
1194 1196 1215 1234 1235 1240 1246 1248 1251 1253 1257 1266 1270 1272 1274
1280 1282 1284 1290 1300 1302 1304 1327 1332 1334 1337 1339 1341 1343 1349
1351 1353 1355 1359 1363 1365 1367 1368 1374 1376 1381 1383 1398 1400 1402
1407 1409 1411 1416 1418 1419 1422 1424 1427 1432 1435 1437 1446 1452 1459

Please can you help me adjust the script the capture all the carriage returns and convert all the data.

Many Thanks,

I've adjusted the code and tested it with long data strings 500+ lines works great!

Thanks for your help.

Here's my revised code:

Please Login or Register  to view this content.