converting text to value

[dtaylor]

each month i download text files off an aops mainframe system and then do calucations in access with the data. my prob is that when i export the data to excell the numbers do not convert to values (even in access they do not convert. If I try to force the format while importing the data to Access I lose everything in that column). Example..a cell that should read .10% reads just .10 and will not convert to a percentage. I have to manually do a values(trim()) formula to convert the cells to values. Is there a way to convert all the cells of a certain column to a value and apply a percentage format in VBA? Any help would be greatly appreciated! Thanks

[Al Chara]

The following code will convert all cells in Column A to values and then to percentages. You might only need the code to convert to a percentage.

Columns(1).NumberFormat = "0.00"
Columns(1).NumberFormat = "0%"

[dtaylor]

thanks for the quick response, unfortunetley it did not work. to test the code i manually placed a .10 in the first cell of the column, ran the code and the number i manually typed changed to the correct format but the balance did not.

[Al Chara]

What do you mean by, "balance did not"?
If you activate one of the cells and click on Format,Cells,Number tab, what format is highlighted?

[dtaylor]

What I meant was that the data from the text file did not convert when applying the code. I tested it by manually placing a number in the first free cell of the problem column and then running the code (not every cell has an entry). The cell that I typed a number in converted to a percentage but the rest or "balance" did not. Under format cells the data type is reading as "General". I changed the type to percantage but nothing. What I do to get around this is an =Values(AO2) formula changing the cells in the column to a value (once I do this I am able to apply a percentage format), then copy/pastespecial the converted data and replaceing the bunk formated data. Do appreciate your help and I hope I was clear.

[Jay Petrulis]

On 2002-03-22 13:44, dtaylor wrote:
What I meant was that the data from the text file did not convert when applying the code. I tested it by manually placing a number in the first free cell of the problem column and then running the code (not every cell has an entry). The cell that I typed a number in converted to a percentage but the rest or "balance" did not. Under format cells the data type is reading as "General". I changed the type to percantage but nothing. What I do to get around this is an =Values(AO2) formula changing the cells in the column to a value (once I do this I am able to apply a percentage format), then copy/pastespecial the converted data and replaceing the bunk formated data. Do appreciate your help and I hope I was clear.

1. Select an unused, blank cell and Copy it.
2. Highlight the column (selection, range, etc.) with the numbers Excel thinks is text.
3. Edit>PasteSpecial> and select the ADD radio button from the Operation area
4. Choose OK.

This should coerce your cells into numbers.

This can also be done in code.

HTH,
Jay

[Yogi Anand]

Hi dtaylor:
It appears that when you are importing data into Excel, it is being formatted as text. So, this is what you want to do...
1) Let us say your imported data is in cells B2 through B6
2) Key in 1 in any cell, say D1
3) highlite D1 then EDIT|COPY
4) then highlite B2:B6 and EDIT|PASTE_SPECIAL|multiply
5) format B2:B6 as percentage
Your results should look like ...

original data
0.1
0.2
0.15
0.3
0.32

data after EDIT|PASTE_SPECIAL|multiply

10%
20%
15%
30%
32%

I hope this is what you wanted ... please post back if this works for you, otherwise explain it a little further and we will take it from there!

[dtaylor]

Great! Both worked and I cut out a step. Now can this be done in code? I am working with a huge amount of files and would like to add code to a template that takes care of this without doing any formulas or pastespecial?
Once again thanks for your help!

[ This Message was edited by: dtaylor on 2002-03-22 14:44 ]

[Jay Petrulis]

Hi,

Something like this should work...

---begin VBA---
Sub test()
Dim cell As Range

For Each cell In Selection
cell.Value = cell + 0
Next cell

End Sub
---end VBA---

HTH,
Jay

[dtaylor]

thanks the code worked. true saviors to mankind! have a great weekend..