
Dashboards
Power Pivot

June 25, 2016

Hello,
I received a spreadsheet where the original author used the following formats on a column
#,##0.00 "TB"
#,##0.00 "GB"
#,##0.00 "MB"
#,##0.00 "KB"
As far as I can tell he applied these formats individually to each cell. I want to extract the TB, GB, MB and KB into another column. I have tried using the "Cell" function, "Value" function, "Right" function, but none of them seem to detect the alpha string in the cell - just the numeric value. I know that Excel recognizes the string in some way, because it allows me to filter on those values.
How can I extract the byte sizes (TB, GB, etc.) into another column?
Thank you in advance for your assistance!

VIP

Trusted Members

June 25, 2016


Dashboards
Power Pivot

June 25, 2016

Hi Sunny,
Your #1 suggestion is what I did. I was just hoping to learn a new formula that would allow me to extract the info as the file changes.
Unfortunately, I cannot download your attachment - I get an error. Could you post the macro and I will put it into my own sheet?
Thank you!
Pam

VIP

Trusted Members

June 25, 2016


VIP

Trusted Members

December 7, 2016

Hi Pam,
Do check out this blog from Philip, it contains lots of useful stuff.
https://www.myonlinetraininghu.....-functions
What you can do is to use =GET.CELL function in a defined name to extract the info you want.
Let's say your data is in cells A1 to A4 in Sheet1, go to name manager and define a new name, let's give it the name GetSize, in the Refers to field, type in following formula: =RIGHT(GET.CELL(53,Sheet1!A1),2)
In cell B1 you now only type in =GetSize and you are good to go, drag and copy down to cells B2 to B4 and you have the result for all four.
Br,
Anders
1 Guest(s)
