Excel Functions I can't live without
So it's my website, and I want to keep these references in one spot so I can reference them later 😂
1- Multiple find and replace for big data. It is pretty much the best way to do cross references for anything. I have pushed the limit for this one and have done up to 800,000 at one time (it took about an hour though for the macro to finish though).
2- Concatenate for making html tables.
1. Multiple Find and Replace
Here is the code from the video if it ever gets taken down.
Sub MultiFindNReplace() 'Update 20140722 Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole Next Application.ScreenUpdating = True End Sub
2. Concatenate
=CONCATENATE (a1,b1,c1)
Turn multiple cells into one. Excel is one of the best programs for making html tables.
Also don't forget spaces or commas.
example:
| <tr><td> | Honda | </td><td> | B92P | </td></tr> | =concatenate(a1,b1,c1,d1,e1) | <tr><td>Honda</td><td>B92P</td></tr> |