Web scraping in VBA and Excel

Question

I'm looking to download data tables from websites into Excel and pull specific pieces of data into a separate worksheet to create a database. I'm having trouble parsing numbers that are imported into one cell in Excel. For example, the numbers "-7 -110" written exactly like that on the website are inputted into one cell in Excel as "=-7-110" yielding the cell contents "-117". When the cell is highlighted, the original =-7-110 displays in the formula bar but I do not know how to either a) input the data as-is and turn-off the autoformatting by excel or b) grab the specific cell contents without the formula being formed from the cell.

Any help is appreciated - thanks!

Jason

Answer 1

Microsoft offers three methods to counter-act built-in number formatting:

Avoiding Automatic Number Formatting If you want to type a value such as 10e5, 1 p, or 1-2, and you do not want the value to be converted to a built-in number format, type the number as a text value. To type a number as a text value, use any of the appropriate methods below.

Method 1
Place a space at the beginning of the entry. NOTE: This method does not work if the entry resembles a number formatted in scientific notation. For example, typing 1e9 results in a scientific number.

Method 2

1. Select a range of cells, and then click Cells on the Format menu.
2. Click the Number tab.
3. Click Text, and then click OK.

This method allows you to type data in the selected cells as text. You must perform these steps before you type the numbers in the cells.

Method 3
Precede the entry with an apostrophe.
For example, type the following: '1 p

Answer 2

Adam has covered the options if you're making changes within the workbook.

Of course, you can also prepare the area into which the data is being imported directly from VBA using the

.NumberFormat

property.

To set the NumberFormat property of a range or cell/s to "Text" you can use

.NumberFormat="@"

For example

Range("C:C").NumberFormat = "@"

will convert all cells in column C to "Text" format.

As far as retrieving the "-7-110" if it's entered into a cell with general format (and is displaying the result of the formula i.e -117), you can access the

.Formula

property of the cell.

For example, if the above formula was entered into cell C1, the VBA code below

Range("C1").Formula

or equivalently

Cells(1, 3).Formula

would return the string "=-7-110".