converting text to value

dtaylor

Active Member
Joined
Mar 21, 2002
Messages
379
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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%"
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top