converting text to value
converting text to value
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: converting text to value

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    So Cal
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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%"
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    So Cal
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    So Cal
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    So Cal
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    So Cal
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com