Formatting Data

[bdc138]

Hello,

I have a ton data in a comma delimited txt file like shown below "Orig Format" I'm trying to convert it to a list of 6 digit numbers with spaces for missing numbers. At the end of each set, I need to place ascending numbers in place of ,, which need the format _ _ _ . Any help would be appreciated. I'm not a programmer but their is no other feasible way to convert all these numbers. Thanks.

Please note that there were some formatting issues during post.

Brad

Orig Desired
Format Format

48,20,1 48201
40,10,1 40101
48,12,1 48121
30,12,1 30121
30,14,1 30141
32,18,1 32181
40,14,1 40141
31,8,1 31 81 'note space
48,12,1 48121
32,18,1 32181
48,12,1 48121
30,18,1 30181
48,12,1 48121
48,14,1 48141
48,12,1 48121
48,10,2 48102
21,6,1 21 61
21,60,1 21061
,, 0 1
48,20,1 48201
48,16,1 48161
40,10,1 40101
40,10,2 40102
30,20,1 30201
30,18,1 30181
30,18,2 30182
40,12,1 40121
40,14,1 40141
30,18,1 30181
48,10,1 48101
48,12,1 48121
48,16,1 48161
21,20,2 21202
30,20,1 30201
30,20,1 30201
30,18,1 30181
,, 0 2
48,18,1 48181
21,2,2 21 22
48,16,2 48162
54,6,1 54 61
48,12,1 48121
21,10,2 21102
51,12,1 51121
21,12,1 21121
30,20,1 30201
21,16,2 21162
21,4,1 21 41
51,14,1 51141
30,20,1 30201
48,10,1 48101
48,12,1 48121
48,16,1 48161
,, 0 3
32,18,3 32183
30,24,2 30242
1,26,1 01261 'note 01
48,12,1 48121
21,8,1 21 81
32,18,1 32181
48,12,1 48121
40,12,1 40121
48,14,1 48141
32,18,1 32181
1,22,2 01222 'note 01
21,6,1 21 61
30,20,1 30201
,, 0 4
48,12,1 48121
21,12,1 21121
21,10,1 21101
21,4,2 21 42
21,8,1 21 81
48,8,1 48 81
48,12,1 48121
48,12,1 48121
21,14,1 21141
48,10,1 48101
,, 0 5

 

[EBGreen]

Why does this one:
1,26,1 01261 'note 01
get a 01 whereas this one:
21,8,1 21 81
gets a (space)8?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[bdc138]

If number corresponding to #,--,- is only one digit, then a zero should be added before it. If the number corresponding to --,#,- is one digit, a space should be added before it. That's just the way it needs formatted. Thanks.

Brad

 

[johnwm]

Use Split function to get the 3 values, then Len function to determine whether to add appropriate character to each part. Then you can manually concatenate the array elements, or use the Join function. All those functions have examples in VBHelp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[bdc138]

Thanks johnwm I'll try to do that.