Thread: Trimming and then calculating (2000/SP3)

1. 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. 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. 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. 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.

5. 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
•