Useful Tips for Excel

Here are some commonly used Excel formulas, you may run into need:

1. Extract a portion of String - Left side of a match pattern
   Formula: =LEFT(A1,SEARCH("MyTest",A1)-1)
   A1 - the cell on which value to run this formula; It can be replaced with String value as needed
   MyTest- the search term
   Input: This is very tricky MyTest shows it rightly!
   Output: This is very tricky
   
   
2. Extract a portion of String - Right side of a match pattern
   Formula: =RIGHT(A1,LEN(A1)-SEARCH("MySecondTest",A1)-LEN("MySecondTest"))
   A1 - the cell on which value to run this formula; It can be replaced with String value as needed
   MySecondTest- the search term
   Input: This is very tricky MySecondTest shows it rightly!
   Output: shows it rightly!
   
   
3. Storage Conversion- Convert all types to Byte
   Formula: =IF(TRIM(RIGHT(A1,2))="B",LEFT(A1,LEN(A1)-2),LEFT(A1,LEN(A1)-2)*(1024)^MATCH(RIGHT(TRIM(A1),2),{"KB","MB","GB"},0))
   A1 - the cell on which value to run this formula; It can be replaced with String value as needed
   
   Input: 1.1 GB
   Output: 1181116006
   
   
4. Storage Conversion- Convert Byte to Relevant size
   Formula: =TEXT((A1/(1024^INT(LOG(A1,1024)))),"0.000")&CHOOSE(INT(LOG(A1,1024))," KB"," MB"," GB")
   A1 - the cell on which value to run this formula; It can be replaced with String value as needed
   
   Input: 1181116006
   Output: 1.1 GB