Excel VBA Strings of Numeric Characters as Numbers and not Strings

Thank you for looking at my question,

I am using VBA to extract inventory data from a large (468400 lines 42,144kb) csv file clean it up and write it to a .xlsx worksheet (9500 lines, 2,100KB)

Many of the part numbers in the file are all numeric characters. After the process finished I opened the .xlsx file and found that the all-numeric part numbers have been processed as if they were numbers (aligned to the right of the column) in a left-aligned column of predominantly text values. Some large numbers have even been output in standard form / scientific notation eg

100175264000499000 is in the worksheet as 1.00175E+17 - this is a real pain because

100175268910209000 is also shown as 1.00175E+17

Can anybody tell me how to force Excel to handle the strings of numbers as strings and not numbers please?
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
In the CSV upload, force that column to be recognised as text.
0
Rgonzo1971Commented:
Hi

if you are using  QueryTables.add you could use

 .TextFileColumnDataTypes = _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
to determine the type of data

Set shFirstQtr = Workbooks(1).Worksheets(1) 
Set qtQtrResults = shFirstQtr.QueryTables _
        .Add(Connection := "TEXT;C:\My Documents\19980331.txt", _
        Destination := shFirstQtr.Cells(1, 1))
With qtQtrResults
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileFixedColumnWidths = Array(5, 4, 5)
        .TextFileColumnDataTypes = _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
    .Refresh
End With

Open in new window

Regards
0
Gary CroxfordOperations Support AnalystAuthor Commented:
Hi Rob, It's not quite as straightforward as that, each line in the csv file has only one comma at the end so there is only one column(see below).

I have to grab each line as a string and then narrow down to the data I need.


extract from csv file:
Item Selection      :     0940012389                  Cut Tube Q250,
,
Item Warehouse Status         : Active                     Order System             : Planned,
Use Item Ordering Data        : No                    Order Method             : Lot for Lot,
Update Inventory/Order Data   : No                    Order Quantity Increment      :         0.0000 [pcs],
Exclude from Cycle Counting   : No                    Minimum Order Quantity        :         0.0000 [pcs],
Storage Zone             :                       Maximum Order Quantity        :  99999999.0000 [pcs],
Default Location Type for                             Fixed Order Quantity     :         1.0000 [pcs],
Inbound Advice           : Bulk                       Economic Order Quantity       :         1.0000 [pcs],
Unit                : pcs       piece                 Maximum Inventory        :  99999999.0000 [pcs],
Package Definition       :                       Reorder Point            :         0.0000 [pcs],
Inv.Carrying Costs per Year   :       0.0000 [GBP]              Safety Stock             :         0.0000 [pcs],
Order Costs              :       0.0000 [GBP]              Order Interval           :    0.00 Days,
Inventory Valuation Method    : Mov. Aver. Unit Cost (MAUC)          Safety Time              :    0.00 Hours,
Valuation by Wareh. Val. Group: Yes
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rgonzo1971Commented:
Not knowing how you separate the number

you could try to add a '

Range("YourRange").Value = "'" & YourNumber 'to force a number as text
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Try this.

Sub GetData()

Dim FF As Integer
Dim strLine As String
Dim lngNR As Long

FF = FreeFile

Open "C:\temp\28704540.csv" For Input As FF

Do While Not EOF(FF)
    Line Input #FF, strLine
    lngNR = lngNR + 1
    Cells(lngNR, "A") = strLine
Loop
Close

End Sub

Open in new window

0
aikimarkCommented:
You can parse the entire string with a regular expression object.  Based on what you posted, the following pattern:
(?:^|\n)(?:(\w[^:]+?\S)\s*:\s{1,9}(\w[^,:]*?\S)?\s{8,}(\w[^:,]+?\S)\s*:\s{1,9}(\w[^,:]*?\S)|(\w[^:,]+?\S)\s*:\s{1,9}(\w[^,:]*?\S))(?:,|$|\r)

Open in new window

parses your data like this:
Match 0 Start(0) Length(68) 

	SubMatch 0: 
	SubMatch 1: 
	SubMatch 2: 
	SubMatch 3: 
	SubMatch 4: Item Selection
	SubMatch 5: 0940012389 Cut Tube Q250

Match 1 Start(72) Length(95) 

	SubMatch 0: Item Warehouse Status
	SubMatch 1: Active
	SubMatch 2: Order System
	SubMatch 3: Planned
	SubMatch 4: 
	SubMatch 5: 

Match 2 Start(168) Length(94) 

	SubMatch 0: Use Item Ordering Data
	SubMatch 1: No
	SubMatch 2: Order Method
	SubMatch 3: Lot for Lot
	SubMatch 4: 
	SubMatch 5: 

Match 3 Start(263) Length(108) 

	SubMatch 0: Update Inventory/Order Data
	SubMatch 1: No
	SubMatch 2: Order Quantity Increment
	SubMatch 3: 0.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 4 Start(372) Length(108) 

	SubMatch 0: Exclude from Cycle Counting
	SubMatch 1: No
	SubMatch 2: Minimum Order Quantity
	SubMatch 3: 0.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 5 Start(481) Length(103) 

	SubMatch 0: Storage Zone
	SubMatch 1: 
	SubMatch 2: Maximum Order Quantity
	SubMatch 3: 99999999.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 6 Start(585) Length(103) 

	SubMatch 0: 
	SubMatch 1: 
	SubMatch 2: 
	SubMatch 3: 
	SubMatch 4: Default Location Type for Fixed Order Quantity
	SubMatch 5: 1.0000 [pcs]

Match 7 Start(689) Length(108) 

	SubMatch 0: Inbound Advice
	SubMatch 1: Bulk
	SubMatch 2: Economic Order Quantity
	SubMatch 3: 1.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 8 Start(798) Length(103) 

	SubMatch 0: Unit
	SubMatch 1: pcs piece
	SubMatch 2: Maximum Inventory
	SubMatch 3: 99999999.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 9 Start(902) Length(98) 

	SubMatch 0: Package Definition
	SubMatch 1: 
	SubMatch 2: Reorder Point
	SubMatch 3: 0.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 10 Start(1001) Length(113) 

	SubMatch 0: Inv.Carrying Costs per Year
	SubMatch 1: 0.0000 [GBP]
	SubMatch 2: Safety Stock
	SubMatch 3: 0.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 11 Start(1115) Length(100) 

	SubMatch 0: Order Costs
	SubMatch 1: 0.0000 [GBP]
	SubMatch 2: Order Interval
	SubMatch 3: 0.00 Days
	SubMatch 4: 
	SubMatch 5: 

Match 12 Start(1216) Length(111) 

	SubMatch 0: Inventory Valuation Method
	SubMatch 1: Mov. Aver. Unit Cost (MAUC)
	SubMatch 2: Safety Time
	SubMatch 3: 0.00 Hours
	SubMatch 4: 
	SubMatch 5: 

Match 13 Start(1328) Length(36) 

	SubMatch 0: 
	SubMatch 1: 
	SubMatch 2: 
	SubMatch 3: 
	SubMatch 4: Valuation by Wareh. Val. Group
	SubMatch 5: Yes

Open in new window

1
Gary CroxfordOperations Support AnalystAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.