Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trimming and then calculating (2000/SP3)

    I have a list of disk space usage, similar to the attached example worksheet. Each entry has a number followed by a two-letter abbreviation. I need to convert this list to numbers only which can be compared. Currently, I see this as a 4 step process.
    1) Copy the abbreviations into their own column.
    2) Remove the abbreviations from the original column.
    3) Convert the original column from text to numbers
    4) Create a nested if that specifies a specific multiplier for the original number, based on the abbreviation.

    I know how to do steps 1, 3, and 4. It's step 2 that has me stymied. I know there has to be a way to trim the last two characters from a cell, but I can't seem to find it.

    Thank you!
    DeNae

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trimming and then calculating (2000/SP3)

    I wouldn't replace the original column with numeric values, but calculate those in an extra column. To extract the number from A2, use this formula:

    =VALUE(LEFT(A2,LEN(A2)-3))

    and fill down as far as needed. To obtain the multiplier, you can also use a lookup table, for example:

    <table border=1><td>Abbreviation</td><td>Bytes</td><td>B</td><td align=right>1</td><td>KB</td><td align=right>1024</td><td>MB</td><td align=right>1048576</td><td>GB</td><td align=right>1073741824</td><td>TB</td><td align=right>1.09951E+12</td></table>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Trimming and then calculating (2000/SP3)

    To expand on Hans' suggestions:
    If you put his "table" in the range H1:I6
    You can put the formula in C2:
    <pre>=VALUE(LEFT(A2,LEN(A2)-3))*VLOOKUP(RIGHT(A2,2),$H$1:$I$6,2,0)</pre>


    and copy it down the column to get your values.

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trimming and then calculating (2000/SP3)

    Your formula in column B, andHans' and Steve's formula will fail if you have any strings with a single character at the end, like 512 B for 512 bytes. See if my correction to your formula in column B and my formula in column C of the attached workbook do what you want.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Trimming and then calculating (2000/SP3)

    Good Catch. I didn't try enough variants (I only used the examples given...)

    Steve

Posting Permissions

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