Text to column fixed length

[Luci]

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

 

[IanC]

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.

 

[Luci]

Hi Ian,

Unfortunately the data is not the same length which is the issue. Any such
porgram I can upload?

 

[Dave Peterson]

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.

 

[IanC]

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?

 

[Luci]

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

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?

Hi Ian,

Data looks something like above, as you can see is a bit of a mess !

Thanks, Luci

 

[IanC]

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