excel web query problem

[ryan010101]

I created an asp web page to manipulate data and I'm using a web query to get that result into a spreadsheet. The problem is two of the fields, stock# and UPC, have leading zeros. When importing via the web query it imports it as a number which get's rid of the leading zero. Does anybody know of a way to make it import as text so the leading zeros are not gotten rid of?

thanks

 

[TBrewerton]

Hi, Ryan, im assuming this is done through excel... if so a way of doing what you ask would be to import the query to a different place maybe a new sheet or somewhere on the current one where you can hide it... now if you open up VB(a) and start a new module and put in this code...

Function EXTRACTNUMBER(TARGET As Range, Optional NUM As Boolean = True) As Variant
Dim n, TmpVal

If TARGET.Cells.Count > 1 Or Len(TARGET) < 1 Then
EXTRACTNUMBER = CVErr(1)
Exit Function
End If

For n = 1 To Len(TARGET)
If NUM = True Then
Select Case Mid(TARGET, n, 1)
Case 0 To 9: EXTRACTNUMBER = EXTRACTNUMBER & Mid(TARGET, n, 1)
End Select
Else
Select Case Mid(TARGET, n, 1)
Case 0 To 9:
Case Else: EXTRACTNUMBER = EXTRACTNUMBER & Mid(TARGET, n, 1)
End Select
End If
Next n

End Function

Now that code is used to get an exact number from a cell for instance say in cell A1 is the following "HI123" if you typed the formulae =EXTRACTNUMBER(A1) into a cell it will insert just the 123... i havent played with this function much but i think i was toled it also can do the oppersite, i hope that helps, by the way for copy right purposes that code was posted on the MrExcel forums.

Hope that helps and good luck