I have a simple list of numbers signifying terabytes (T), gigabytes (G), megabytes (M) or kilobytes (K) of data. The fields contain both a number with a text character, no space between them. I need to remove the text character and convert the remaining number to terabytes. So "900G" becomes ".900", "500M" becomes ".0005" etc. I've attached the example. Any help with this would be greatly appreciated.
Thanks!
Last edited by PWM; 04-08-2014 at 08:07 PM.
Is there only 6 possible characters to deal with? If so a nested if would work.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
Select the column,
Find and replace T with E+0 (or nothing)
Find and replace G with E-3
Find and replace M with E-6
...
Or, if you want a formula, in B3 and copy down,
=LOOKUP(9E+307, --SUBSTITUTE(A3, {"T","G","M","K"}, "E-" & {0,3,6,9}))
Last edited by shg; 04-09-2014 at 10:57 AM. Reason: Corrected cell reference
Entia non sunt multiplicanda sine necessitate
Thanks. I should have clarified that I'm working with a very large list, which is updating daily so I need to be able to automatically achieve the result via a formula. Doing it manually with Find isn't feasible for me.
Sorry, I see that you did include a formula. My oversight. I'll give it a try!
Thank you!
Can you walk through what that formula does shg?
Sure.
It does the same thing the Find & Replace does, attempting all four substitutions. Only one of the four will work, and that's the one that the double-negation can convert to a number. The LOOKUP function always matches the lookup value with an array value of like type (here, a number), so it returns the value that works.
I've tried the formula but it returns "0.000000000". I don't understand it, so perhaps I'm using it incorrectly.
What I think I need is a formula in column B that looks in column A and if it sees a "G" it removes the G and divides the remaining number by 1000 returning the result in column B. If it finds a "T" it removes it and does nothing further, if it finds an "M" it removes it and divides number by 1,000,000, and if it finds a "K" it removes it and divides by 1,000,000,000.
Usage 994.0G B3: =LOOKUP(9E+307, --SUBSTITUTE(A3, {"T","G","M","K"}, "E-" & {0,3,6,9})) 12.3G 154.9M 64.5K 46.1T 4.2T
Sorry, the B3 in the formula should have been A3.
Works great! Thank you. I'm a newbie, so the formula is quite foreign to me. What are some research topics that would help me understand it? The "9E+307" for example. But again, many thanks!
9E307 is close to the largest number Excel allows you to enter via the user interface (9.99999999999999E307). It just causes the LOOKUP function to return the largest (and only) number in the list.
9E+307 is the same as 9*(10^307) which is just a REALLY big number.
redundant post! lol
shg is on point here....
So scientific notation. Why do we want the LOOKUP to return the largest (and only) number in the list? What list? Sorry - as I said I'm a newbie.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks