Formula: CONCATENATE
Input: <a href="http://www.uniprot.org/uniprot/" target="_blank">Swissprot: </a>
Output: <a href="http://www.uniprot.org/uniprot/Q16552" target="_blank">Swissprot: Q16552</a>Human
Formula: Clean
Input: =clean(column name)
Output: cleaning unwanted character
Formula: Vlookup
Input: =iferror(vlookup(A1,sheet1!A1:A100,1,0),iferroor(vlookup(A1,sheet2!A1:A100,1,0),""))
Output: Lookup value show in a cell
Formula: Remove last Comma( , )
Input: =LEFT(A1,LEN(TRIM(A1))-(RIGHT(TRIM(A1))=","))
Output: Last comma remove in a cell
Formula: Find fix word
Input: =IF(ISERROR(FIND("This product contains",A1,1))=TRUE,0,A2)
Output: Find as you like word in a perticular cell
Formula: Find fix word and delete after fixword
Input: =LEFT(A1,FIND("using ",A1)-2)
Output: Find fix word in a xecel cell delete after all fix word
Formula: Remove last comma after all character
Input: =LEFT(A1,FIND("@",SUBSTITUTE(A1,",","@",(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/LEN(",")))-1)
Output: Remove last comma in a single cell and delete after comma all character
Formula: Find fix word in many times in excel
Input: =(LEN(TRIM(SUBSTITUTE(A1," ",""))) - LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,"alpesh",""))," ","")))/LEN("alpesh")
Output: Find fix word many times in excel
Formula: Only (bracket) text cut and paste formula
Input: =LEFT(RIGHT(A1,LEN(A1) - FIND("(",A1)), FIND(")",RIGHT(A1,LEN(A1) - FIND("(",A1)))-1)
Output: Bracket text value paste in a different cell where you use this formula
Formula: Find Fix word and after fix word split in new cell
Input: =TRIM(RIGHT(SUBSTITUTE(A1," 20mM","@20mM"),LEN(A1)-FIND("@",SUBSTITUTE(A1," 20mM","@20mM"))))
Output: Find fix word in a xecel cell and after fix word paste in to new cell where you use this formula
Formula: Clean function macro
Input: Sub exa()
Dim rCell As Range
For Each rCell In
ActiveSheet.UsedRange
rCell.Value = Replace(rCell.Value, Chr(10), " ")
Next
End Sub
Formula: Price discount formula
Input: K2 : 150 price
20% discount price
=K2-(K2*0.2)
Formula: Remove Duplicates from a single cell
Input: Function CustomGT(txt As String, Optional delim As String = " ") As String
Dim e
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each e In Split(txt, delim)
If Trim(e) <> "" And Not .exists(Trim(e)) Then .add Trim(e), Nothing
Next
If .Count > 0 Then CustomGT = Join(.keys, delim)
End With
End Function
[2] Use this fromula in particular cell.
=CustomGT(A2,",")
