Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to column fixed length
Hi,
I have scanned data from an unknown source at this stage that I need to convert to excel. I can get the data ok but it is all over the place. Text to columns does not work accurately either or using fixed width or delimiters with a space or anything else. What will work given the data I have is if I can set the length of the columns for the text, ie column 1, 12 characters, column 2, 25 characters, etc, etc. Is there a free download available for this function or does it potentially exist somewhere? If it was a one off I wouldnt bother but we are receving many sheets like this. Thanks so much, Luci |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to column fixed length
You can manually select the division point between columns if you use fixed
width. Of curse, this will only work if all the data is the same width. -- Ian -- "Luci" wrote in message ... Hi, I have scanned data from an unknown source at this stage that I need to convert to excel. I can get the data ok but it is all over the place. Text to columns does not work accurately either or using fixed width or delimiters with a space or anything else. What will work given the data I have is if I can set the length of the columns for the text, ie column 1, 12 characters, column 2, 25 characters, etc, etc. Is there a free download available for this function or does it potentially exist somewhere? If it was a one off I wouldnt bother but we are receving many sheets like this. Thanks so much, Luci |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to column fixed length
Hi Ian,
Unfortunately the data is not the same length which is the issue. Any such porgram I can upload? "Luci" wrote: Hi, I have scanned data from an unknown source at this stage that I need to convert to excel. I can get the data ok but it is all over the place. Text to columns does not work accurately either or using fixed width or delimiters with a space or anything else. What will work given the data I have is if I can set the length of the columns for the text, ie column 1, 12 characters, column 2, 25 characters, etc, etc. Is there a free download available for this function or does it potentially exist somewhere? If it was a one off I wouldnt bother but we are receving many sheets like this. Thanks so much, Luci |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to column fixed length
What you describe in your original post is the definition of fixed width.
Field 1 is 12 characters. Field 2 is 25 characters. etc, etc. Maybe you should explain the situation again. Luci wrote: Hi Ian, Unfortunately the data is not the same length which is the issue. Any such porgram I can upload? "Luci" wrote: Hi, I have scanned data from an unknown source at this stage that I need to convert to excel. I can get the data ok but it is all over the place. Text to columns does not work accurately either or using fixed width or delimiters with a space or anything else. What will work given the data I have is if I can set the length of the columns for the text, ie column 1, 12 characters, column 2, 25 characters, etc, etc. Is there a free download available for this function or does it potentially exist somewhere? If it was a one off I wouldnt bother but we are receving many sheets like this. Thanks so much, Luci -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to column fixed length
How do you determine where one section ends and the next begins? Is it at a
specific character, after a single word, specific number of words etc? -- Ian -- "Luci" wrote in message ... Hi Ian, Unfortunately the data is not the same length which is the issue. Any such porgram I can upload? "Luci" wrote: Hi, I have scanned data from an unknown source at this stage that I need to convert to excel. I can get the data ok but it is all over the place. Text to columns does not work accurately either or using fixed width or delimiters with a space or anything else. What will work given the data I have is if I can set the length of the columns for the text, ie column 1, 12 characters, column 2, 25 characters, etc, etc. Is there a free download available for this function or does it potentially exist somewhere? If it was a one off I wouldnt bother but we are receving many sheets like this. Thanks so much, Luci |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to column fixed length
CODE DESCRIPTION QTY CASES $$ CASES $$ CASES $$ CASES $$ CASES $$ 203705 PEN GEL RETRACTABLE BLUE RT11 12 16 271.20 33 559.35 50 847.50 14 237.30 113 1915.35 203860 PEN GEL RETRACTABLE BLK RT11 12 23 389.85 34 576.30 49 830.55 6 101.70 112 1898.40 211562 PENS P/MATE 1.2 VARIETY 10PK 12 1 22.90 1 22.90 2 45.80 2 45.80 6 137.40 211910 GROCERY FLR DISPLAY TAX TIME08 1 5 1254.10 7 1755.74 12 3009.84 211928 HANG SELL PRE PACKS BECKHAM 08 1 1 103.84 1 103.84 211936 CONVENIENCE PK BECKHAM SHRP 08 1 2 308.66 20 3086.60 22 3395.26 211944 CANNISTER BECKHAM SHARPIE PK08 1 4 288.00 9 648.00 13 936.00 211986 HANG SELL PRE PACKS TAX TIME08 1 1 156.05 1 156.05 212128 PEN KILOMETRICO BLU/RD/BLK10PK 12 79 2034.25 57 1467.75 83 2137.25 32 824.00 251 6463.25 236172 CORRECTION FLUID WHT 3100 HS 12 81 1132.38 93 1300.14 76 1062.48 60 838.80 310 4333.80 238629 CORRECTION PEN L/PAPER HS 12 40 1080.80 52 1405.04 51 1378.02 32 864.64 175 4728.50 243674 PEN P/MATE ROLLERBALL ASTD 4PK 12 6 303.00 5 252.50 1 50.50 12 606.00 244557 PEN KILOMETRICO BLUE PK5 12 76 1277.56 82 1378.42 99 1664.19 63 1059.03 320 5379.20 244905 PEN P/MATE PROFILE ASTD 4PK 6 6 151.50 14 353.50 15 378.75 3 75.75 38 959.50 244971 PEN P/MATE PROFILE BLUE 2PK 6 3 38.34 8 102.24 13 166.14 7 89.46 31 396.18 244989 PEN P/MATE PROFILE BLACK 2PK 6 3 38.34 15 191.70 12 153.36 5 63.90 35 447.30 245317 LETRATAG CLIPSTRIP PACK 1 1 193.24 1 193.24 246169 HIGHLIGHTER MINI SHARPIE 4PK 6 13 211.38 17 276.42 28 455.28 9 146.34 67 1089.42 247408 PENCIL VIBZ MECHANICAL 5PK 6 9 174.96 17 330.48 31 602.64 6 116.64 63 1224.72 247474 CORRECTION TAPE MINI 3PK 6 5 97.20 15 291.60 26 505.44 15 291.60 61 1185.84 251601 PEN ROLL BALL BLUE 2+2BNS 4PK 12 251619 PEN ROLL BALL BLCK 2+2BNS 4PK 12 5 143.20 13 372.32 30 859.20 5 143.20 53 1517.92 251669 MARKER SHARPIE MINI ASSTD 4PK 6 1 16.26 6 97.56 6 97.56 13 211.38 255485 PENCIL PACER MECH TK3018 HS 12 6 114.72 23 439.76 18 344.16 4 76.48 51 975.12 268894 MARKER CHISEL TIP SHARPIE BLCK 6 21 172.20 38 311.60 54 442.80 28 229.60 141 1156.20 268917 MARKER SUPR SHARPIE 1.5MM BLCK 6 27 221.40 35 287.00 44 360.80 14 114.80 120 984.00 268983 MARKER CD/DVD PAPERMATE BLACK 12 20 406.80 19 386.46 15 305.10 12 244.08 66 1342.44 269824 PEN KILOMETRICO RED MED PK5 12 16 268.96 23 386.63 24 403.44 1 16.81 64 1075.84 269874 PEN KILOMETRICO BLACK MED PK5 12 49 823.69 42 706.02 75 1260.75 25 420.25 191 3210.71 269882 MARKER X/FINE 0.4MM BLK SHARPI 6 24 161.28 25 168.00 56 376.32 21 141.12 126 846.72 272136 RULER LAVA FOOZ SNGL 6 11 139.70 15 190.50 19 241.30 18 228.60 63 800.10 272348 SHARPENER LAVA FOOZ SNGL 6 20 269.60 25 337.00 54 727.92 28 377.44 127 1711.96 272542 ERASER PENCIL PETZ FOOHY SNGL 6 11 77.00 29 203.00 45 315.00 27 189.00 112 784.00 272681 ERASERS SCENTED CAP 2PK 6 5 33.40 5 33.40 11 73.48 7 46.76 28 187.04 272788 FOOHY KIDS BTS P/PK 1 272877 ERASERS SCENTED FOOHEY 30PCE 6 17 77.18 30 136.20 58 263.32 40 181.60 145 658.30 276685 GRIP 4 PEN&PENCIL FOOHY 10PK 6 8 56.00 3 21.00 22 154.00 28 196.00 61 427.00 284418 CORRECTION TAPE PRECISION 6 9 144.72 12 192.96 25 402.00 3 48.24 49 787.92 285749 SHARPIE TWIN-TIP BLACK 6 25 293.25 20 234.60 26 304.98 20 234.60 91 1067.43 285951 SHARPIE FINE ASST 4PK 6 17 414.29 14 341.18 19 463.03 7 170.59 57 1389.09 286177 HIGHLIGHTER TEXT ACCENT YEL2PK 6 18 159.84 20 177.60 31 275.28 9 79.92 78 692.64 286494 SHARPIE CD/DVD MARKER T/PK 1PK 12 6 156.30 14 364.70 18 468.90 5 130.25 43 1120.15 287115 E/RACER RAPID ERASER 2PK 12 38 492.48 26 336.96 67 868.32 17 220.32 148 1918.08 700333 PEN FLEXIGRIP ULTRA MED BLK HS 12 12 169.32 13 183.43 14 197.54 12 169.32 51 719.61 700391 PEN FLEXIGRIP ULTRA MED BLU HS 12 9 126.99 13 183.43 17 239.87 8 112.88 47 663.17 701907 PEN P/MATE FLEXGRP P/RIBN 2007 36 "IanC" wrote: How do you determine where one section ends and the next begins? Is it at a specific character, after a single word, specific number of words etc? -- Ian -- Hi Ian, Data looks something like above, as you can see is a bit of a mess ! Thanks, Luci "Luci" wrote in message ... Hi Ian, Unfortunately the data is not the same length which is the issue. Any such porgram I can upload? "Luci" wrote: Hi, I have scanned data from an unknown source at this stage that I need to convert to excel. I can get the data ok but it is all over the place. Text to columns does not work accurately either or using fixed width or delimiters with a space or anything else. What will work given the data I have is if I can set the length of the columns for the text, ie column 1, 12 characters, column 2, 25 characters, etc, etc. Is there a free download available for this function or does it potentially exist somewhere? If it was a one off I wouldnt bother but we are receving many sheets like this. Thanks so much, Luci |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to column fixed length
I wrote some code to sort this out, but it doesn't work on every line.
Basically, the code is always 6 characters, so that's easy to sort. For the rest, I thought to extract the last section of data recursively (demarcated by a space) and if it's numeric, place it in a column. This works fine for most lines, but comes unstuck when the last section of the description is numeric (eg HANG SELL PRE PACKS BECKHAM 08). The only way I can think to sort this is to check on the number of resulting columns as there should always be an even number (including the original column of data), but I don't know how to achieve this. This method would fail if the description end in TWO numeric values. The code relies on the original data being in column A starting on row 1 and leaves this column unchanged. Maybe someone else can come up with a better suggestion, as the numeric end to the description has me stumped. Watch out for word wrapping. The only lines to start at column 1 are "Sub test()" and "End Sub". Sub test() ' Expands headers using space-delimited text-to-columns Cells(1, 1).TextToColumns Destination:=Cells(1, 2), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True For r = 2 To 100 c = 14 ' original column + 13 expanded data columns x = 1 'extract 6 character code and place it in column B Cells(r, 2).Value = Left(Cells(r, 1), 6) ' place remainder of original string into v variable remainder = Trim(Right(Cells(r, 1).Value, Len(Cells(r, 1).Value) - 6)) ' extract segments of data starting at end of string until segment is no longer numeric While IsNumeric(x) = True x = Right(remainder, Len(remainder) - InStrRev(remainder, " ")) ' if segment is numeric, place it in a column starting at N (column 14) If IsNumeric(x) Then Cells(r, c) = x c = c - 1 ' remove segment and preceding space from remainder of string remainder = Left(remainder, InStrRev(remainder, " ") - 1) End If Wend 'when all numeric data has been extracted, placed remainder in C (column 3) Cells(r, 3).Value = remainder 'remove blank cells from row If Cells(r, 14).Value < "" Then While Cells(r, 4) = "" Cells(r, 4).Delete Shift:=xlToLeft Wend End If Next End Sub -- Ian -- "Luci" wrote in message ... CODE DESCRIPTION QTY CASES $$ CASES $$ CASES $$ CASES $$ CASES $$ 203705 PEN GEL RETRACTABLE BLUE RT11 12 16 271.20 33 559.35 50 847.50 14 237.30 113 1915.35 203860 PEN GEL RETRACTABLE BLK RT11 12 23 389.85 34 576.30 49 830.55 6 101.70 112 1898.40 211562 PENS P/MATE 1.2 VARIETY 10PK 12 1 22.90 1 22.90 2 45.80 2 45.80 6 137.40 211910 GROCERY FLR DISPLAY TAX TIME08 1 5 1254.10 7 1755.74 12 3009.84 211928 HANG SELL PRE PACKS BECKHAM 08 1 1 103.84 1 103.84 211936 CONVENIENCE PK BECKHAM SHRP 08 1 2 308.66 20 3086.60 22 3395.26 211944 CANNISTER BECKHAM SHARPIE PK08 1 4 288.00 9 648.00 13 936.00 211986 HANG SELL PRE PACKS TAX TIME08 1 1 156.05 1 156.05 212128 PEN KILOMETRICO BLU/RD/BLK10PK 12 79 2034.25 57 1467.75 83 2137.25 32 824.00 251 6463.25 236172 CORRECTION FLUID WHT 3100 HS 12 81 1132.38 93 1300.14 76 1062.48 60 838.80 310 4333.80 238629 CORRECTION PEN L/PAPER HS 12 40 1080.80 52 1405.04 51 1378.02 32 864.64 175 4728.50 243674 PEN P/MATE ROLLERBALL ASTD 4PK 12 6 303.00 5 252.50 1 50.50 12 606.00 244557 PEN KILOMETRICO BLUE PK5 12 76 1277.56 82 1378.42 99 1664.19 63 1059.03 320 5379.20 244905 PEN P/MATE PROFILE ASTD 4PK 6 6 151.50 14 353.50 15 378.75 3 75.75 38 959.50 244971 PEN P/MATE PROFILE BLUE 2PK 6 3 38.34 8 102.24 13 166.14 7 89.46 31 396.18 244989 PEN P/MATE PROFILE BLACK 2PK 6 3 38.34 15 191.70 12 153.36 5 63.90 35 447.30 245317 LETRATAG CLIPSTRIP PACK 1 1 193.24 1 193.24 246169 HIGHLIGHTER MINI SHARPIE 4PK 6 13 211.38 17 276.42 28 455.28 9 146.34 67 1089.42 247408 PENCIL VIBZ MECHANICAL 5PK 6 9 174.96 17 330.48 31 602.64 6 116.64 63 1224.72 247474 CORRECTION TAPE MINI 3PK 6 5 97.20 15 291.60 26 505.44 15 291.60 61 1185.84 251601 PEN ROLL BALL BLUE 2+2BNS 4PK 12 251619 PEN ROLL BALL BLCK 2+2BNS 4PK 12 5 143.20 13 372.32 30 859.20 5 143.20 53 1517.92 251669 MARKER SHARPIE MINI ASSTD 4PK 6 1 16.26 6 97.56 6 97.56 13 211.38 255485 PENCIL PACER MECH TK3018 HS 12 6 114.72 23 439.76 18 344.16 4 76.48 51 975.12 268894 MARKER CHISEL TIP SHARPIE BLCK 6 21 172.20 38 311.60 54 442.80 28 229.60 141 1156.20 268917 MARKER SUPR SHARPIE 1.5MM BLCK 6 27 221.40 35 287.00 44 360.80 14 114.80 120 984.00 268983 MARKER CD/DVD PAPERMATE BLACK 12 20 406.80 19 386.46 15 305.10 12 244.08 66 1342.44 269824 PEN KILOMETRICO RED MED PK5 12 16 268.96 23 386.63 24 403.44 1 16.81 64 1075.84 269874 PEN KILOMETRICO BLACK MED PK5 12 49 823.69 42 706.02 75 1260.75 25 420.25 191 3210.71 269882 MARKER X/FINE 0.4MM BLK SHARPI 6 24 161.28 25 168.00 56 376.32 21 141.12 126 846.72 272136 RULER LAVA FOOZ SNGL 6 11 139.70 15 190.50 19 241.30 18 228.60 63 800.10 272348 SHARPENER LAVA FOOZ SNGL 6 20 269.60 25 337.00 54 727.92 28 377.44 127 1711.96 272542 ERASER PENCIL PETZ FOOHY SNGL 6 11 77.00 29 203.00 45 315.00 27 189.00 112 784.00 272681 ERASERS SCENTED CAP 2PK 6 5 33.40 5 33.40 11 73.48 7 46.76 28 187.04 272788 FOOHY KIDS BTS P/PK 1 272877 ERASERS SCENTED FOOHEY 30PCE 6 17 77.18 30 136.20 58 263.32 40 181.60 145 658.30 276685 GRIP 4 PEN&PENCIL FOOHY 10PK 6 8 56.00 3 21.00 22 154.00 28 196.00 61 427.00 284418 CORRECTION TAPE PRECISION 6 9 144.72 12 192.96 25 402.00 3 48.24 49 787.92 285749 SHARPIE TWIN-TIP BLACK 6 25 293.25 20 234.60 26 304.98 20 234.60 91 1067.43 285951 SHARPIE FINE ASST 4PK 6 17 414.29 14 341.18 19 463.03 7 170.59 57 1389.09 286177 HIGHLIGHTER TEXT ACCENT YEL2PK 6 18 159.84 20 177.60 31 275.28 9 79.92 78 692.64 286494 SHARPIE CD/DVD MARKER T/PK 1PK 12 6 156.30 14 364.70 18 468.90 5 130.25 43 1120.15 287115 E/RACER RAPID ERASER 2PK 12 38 492.48 26 336.96 67 868.32 17 220.32 148 1918.08 700333 PEN FLEXIGRIP ULTRA MED BLK HS 12 12 169.32 13 183.43 14 197.54 12 169.32 51 719.61 700391 PEN FLEXIGRIP ULTRA MED BLU HS 12 9 126.99 13 183.43 17 239.87 8 112.88 47 663.17 701907 PEN P/MATE FLEXGRP P/RIBN 2007 36 "IanC" wrote: How do you determine where one section ends and the next begins? Is it at a specific character, after a single word, specific number of words etc? -- Ian -- Hi Ian, Data looks something like above, as you can see is a bit of a mess ! Thanks, Luci "Luci" wrote in message ... Hi Ian, Unfortunately the data is not the same length which is the issue. Any such porgram I can upload? "Luci" wrote: Hi, I have scanned data from an unknown source at this stage that I need to convert to excel. I can get the data ok but it is all over the place. Text to columns does not work accurately either or using fixed width or delimiters with a space or anything else. What will work given the data I have is if I can set the length of the columns for the text, ie column 1, 12 characters, column 2, 25 characters, etc, etc. Is there a free download available for this function or does it potentially exist somewhere? If it was a one off I wouldnt bother but we are receving many sheets like this. Thanks so much, Luci |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'd like to have the X-axis a fixed length. | Charts and Charting in Excel | |||
Fixed Length, Text Formatted | Excel Discussion (Misc queries) | |||
fixed length file | Excel Discussion (Misc queries) | |||
Fixed length text file | Excel Discussion (Misc queries) | |||
Search a Column by text length | Excel Worksheet Functions |