Log In
Register
Home
Dashboards
Power Pivot
June 25, 2016
Offline
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
Offline
Hi Pam
I have 2 suggestions:
1) Filter the values by using the Search box for TB (for example) and then manually filling TB to the right of the filtered cells.
2) Write a custom function (refer attachment)
Hope this helps
Sunny
Dashboards
Power Pivot
June 25, 2016
Offline
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
Offline
Hi Pam
I don't have a problem downloading the file.
Anyway here is the code.
Function GetFormat(rng As Range)
GetFormat = Mid(Right(rng.NumberFormat, 4), 2, 2)
End Function
Sunny
VIP
Trusted Members
December 7, 2016
Offline
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)
