EGTools is an Excel add-in that provides various functions and features to help you use Excel.
It supports new functions added to Excel 2019, 2021, 2024, and Microsoft 365.
The VBA code is different, it cannot be used in Excel for Mac.
Please download the latest version from the Release page.
New versions will continue to be updated with the same name, so just put them in the same folder after downloading.
https://github.com/EGTools/EGTools-Excel/releases/latest
Before the official distribution, there may be cases where improved/supplemented Pre-Release is temporarily uploaded.
Please use it with caution as there may be some errors because the function inspection has not been completed.
https://github.com/EGTools/EGTools-Excel/releases
For inquiries, please use the "Discussions"
Reporting Errors
Q & A
Note
From v4.5.5, supporting for ExcelDna-Intellisense Add-in has been removed.
For instructions on how to install Excel add-ins, please refer to
https://github.com/EGTools/EGTools-Excel/wiki/Install-Excel-Add%E2%80%90in
Warning
Excessive use of UDF can slow down Excel calculations considerably, so it is recommended to change it to a value after the operation.
You can use functions added in higher versions of Excel in lower versions.
- REGEXTEST : Checks if part of text matches a regular expression.
- REGEXEXTRACT : Extracts substrings that match a regular expression.
- REGEXREPLACE : Replaces part of a string with another string using a regular expression.
- TRANSLATE : Translates a string into a specified language.
- DETECTLANGUAGE : Automatically determines the language of a string.
- TRIMRANGE : Removes blank rows and columns from a range/array.
- GROUPBY : Groups along one axis and aggregates associated values.
- PIVOTBY : Groups and aggregates associated values along two axes.
- xPERCENTOF : Calculates the percentage of a given value divided by the total value.
- TEXTSPLIT : Splits a text string using column and row delimiters
- TEXTAFTER : Returns the text that appears after a specified character or string.
- TEXTBEFORE : Returns the text that appears before a specified character or string.
- VSTACK : Adds arrays vertically and returns a larger array.
- HSTACK : Appends arrays horizontally and returns a larger array.
- TOCOL : Returns an array of a single column.
- TOROW : Returns an array of a single row.
- WRAPCOLS : Constructs a new array by wrapping the provided rows or columns of values after a specified number of elements.
- WRAPROWS : Constructs a new array by wrapping the provided rows or columns of values after a specified number of elements.
- CHOOSECOLS : Returns an array rearranged in the specified column order from an array or range.
- CHOOSEROWS : Returns an array with rows rearranged in the specified order in an array or range.
- TAKE : Returns a specified number of consecutive rows or columns from the beginning or end of an array.
- DROP : Excludes a specified number of rows or columns from the beginning or end of an array.
- EXPAND : Expands an array or fills it to specified row and column dimensions.
- VALUETOTEXT : Passes text values unchanged and converts non-text values to text.
- ARRAYTOTEXT : Passes text values in an array without changing them and converts non-text values to text.
- IMAGE : Inserts an image using an image URL uploaded on the Internet or a file name stored on your computer.
- XMATCH : Searches for a specified item in an array or range of cells and returns the relative position of the item.
- XLOOKUP :Find items in a table or range by row.
- XFILTER : Filters a range of data based on conditions you define.
- XSORT : Sorts the contents of a range or array.
- SORTBY : Sorts the contents of a range or array based on the values in the corresponding range or array.
- UNIQUE : Returns a list of unique values from a list or range.
- SEQUENCE : Generates a list of consecutive numbers, such as 1, 2, 3, 4.
- RANDARRAY : Generates a random number array.
- XLET : Assigns a name to the result of a calculation. You can store intermediate calculations, values, or define names.
- IFS : Checks if one or more conditions are met and returns the value corresponding to the first TRUE condition.
- MINIFS : Returns the minimum value for which one or more conditions are met.
- MAXIFS : Returns the largest value where one or more conditions are met.
- CONCAT : Combines text from multiple ranges and/or strings.
- TEXTJOIN : Combines text from multiple ranges and/or strings, including delimiters.
- SWITCH : Evaluates one formula or value and returns the result corresponding to the first matching value.
- FORMULATEXT : Shows the function entered in the specified cell.
- ENCODEURL : Encodes a value so that it can be used by the browser.
- IFNA : Changes to a specified value when there is a #N/A error
- UNICODE : Returns the Unicode code value of the first character.
- UNICHAR : Returns the Unicode character with the specified code value.
- IMPORTRANGE : Import a specified range in Google Sheets
- IMPORTHTML : Imports data by specifying a table or list from an Internet page.
- IMPORTDATA : Import RSS or ATOM feed information
- IMPORTFEED : Reads data from a csv or tsv file.
- GOOGLETRANSLATE : Provides translation using Google's translation service.
- COUNTUNIQUE : Counts the number of unique values in a list of specified values and ranges.
- COUNTUNIQUEIFS : Counts the number of unique values that meet multiple conditions in a specified range.
- QUERY : Executes a search on data in the language used by ADODB.
- EPOCHTODATE : Converts a Unix epoch timestamp to a date and time in Coordinated Universal Time (UTC).
- ISBETWEEN : Checks if a given value is between two other values
- ISEMAIL : Checks if an email address is valid based on the top-level domain
- ISURL : Check if URL value is valid
- MVLOOKUP : Outputs the results of executing Excel's VLOOKUP function in bulk. (mass VLOOKUP)
- MXLOOKUP : Outputs the results of executing Excel's XLOOKUP function in bulk. (mass XLOOKUP)
- ILOOKUP : Gets the image corresponding to the specified sequence number among the values found in the search range. (Image LookUp)
- NLOOKUP : Finds a value with a specified number in a list that matches the value you are looking for in the search range.
- MATCHJOIN : Connects the content that matches the search value or condition using a connection character.
- COMPARELIST : Lists the results of comparing individual values in two lists.
- COMPARELISTM : Lists the results of comparing row-by-row values for two lists.
- SAMPLE : Generates a list by randomly sampling from a specified target range.
- STREXT : Extract or remove numbers, English letters, alphanumeric characters, Korean letters, Japanese letters, and Chinese letters/Chinese characters.
- MATCHJOIN : Creates a single string using the result values corresponding to the matching contents using a 'joiner'.
- TEXTPICK : Splits a string based on a specific delimiter and extracts the value of the desired sequence.
- TEXTBETWEEN : Extracts the content between two specified strings.
- TEXTJOINIF : Concatenates values in the search range that satisfy the conditions into a single string.
- CLEANB : Removes non-printable character codes.
- TRIMENDS : Removes only spaces from both ends.
- COUNTER : Lists the frequency of each element in a range or array of data.
- EVAL : Returns the result of a calculation in Excel for a given string.
- IFVISIBLE : Applies various statistical functions only to visible cells.
- AGGREGATEC : Returns an aggregate that excludes all hidden cells in a list or database.
Note
It was separated into EGqcF.xlam.
- SAMPLINGSIZE : Calculates the number of samples to be tested based on the LOT size, AQL, and testing method.
- SAMPLINGAC : Finds the maximum number of defective units to be inspected based on LOT size, AQL, and inspection level.
- SAMPLINGRE : Calculates the minimum number of defective units to be inspected based on LOT size, AQL, and inspection level.
- SAMPLINGLABEL : Get sample text based on LOT size and inspection level.
Note
Separated into EGBarcode.xlam.
- KOREANHOLIDAYS : Lists public holidays in South Korea.
- TOLUNAR : Converts a solar date to a lunar date.
- TOSOLAR : Converts a lunar date to a solar date.
- DATETIME : Converts a date and time string containing Korean and Chinese characters to a date and time.
- MONTHBYWEEK : Checks the month of the specified week based on the specified day of the week.
- WEEKNUMOFMONTH : Finds the number of weeks in a month based on the specified day of the week.
- JULIANDAY : Calculates the Julian Day Number.
- JDTODATE : Converts a Julian Day Number to a Gregorian date.
- TEXTJOINIFCOLOR : Joins strings using a delimiter if the visible color of the target range is the same color as the reference cell.
- DISPLAYCOLOR : Returns the color number of the background color/text color as the visible color of the target cell.
- SUMIFCOLOR : Adds numbers if the visible color of the target range is the same background color/font color as the reference cell.
- COUNTIFCOLOR : Counts the numbers if the visible color of the target range is the same background color/font color as the reference cell.
- RGB : Calculates a True Color color value using Red, Green, and Blue color values.
- TORGB : Decomposes a True Color color value into Red, Green, and Blue color values.
- UNPIVOT : Converts a pivot table or crosstab to a regular data table.
- JSONPARSE : Finds values matching a path name in a JSON string.
- JSONTOARRAY : Converts each step and value of a JSON string's pathname into an array.
- JSONPAI수 : Lists a JSON string as pathname-value pairs.
- EXRATE : Check the foreign exchange rate for the South Korean Won
- EXPLODE : Lists the columns you specify by breaking them down into delimiters.
- TEXTNUMSORT : When sorting data that contains mixed letters and numbers, sorts the numbers as numbers.
- PAPAGOTRANSLATE : 네이버의 Papago API를 이용한 번역을 제공합니다
- RZ : 0이나 빈셀, 오류를 빈문자열("")로 변환합니다. (Remove Zero)
- IFERRORX :
- HANTONUMBER : Converts numbers entered in Korean, Chinese characters, and various Chinese characters to Arabic numerals.
- US32TODEC : Converts the 32-fraction representation of the US bond market to decimal.
- DECTOUS32 : Converts a regular number to the 32-digit representation of the U.S. bond market.
- SEARCHADDRESS : Search for information by road name address.
- ZIPCODE : Searches for zip codes, road names, and land addresses using keywords such as road name addresses or building names.
- GEOPOINT : Check the map coordinates of an address based on the road name address.
- GEOCONVERT : Converts map coordinates to another coordinate system.
- GEODISTANCE : Roughly calculates distance using map coordinates
- OILPRICE : OPINET Search for the average oil price by region and type.
- GASSTATION : OPINET Search for nearby oil prices using
- BRNSTATUS : Check the current status of the business registration number using the National Tax Service API.
- SHEETSLIST : Creates a list of sheets in the current Excel file.
- IPINFO : IP Address basic information
- DIRFOLDER : Outputs a list of files in a specified folder.
- IMPORTURL : Displays the source of an Internet page
- Copy Visible Cells: A function to copy only the cells visible on the screen.
- Copy All: Ability to copy both visible and invisible cells on the screen.
- The above two functions must be performed first before pasting into the visible cells below.
- Paste All: Paste only visible cells with both cell format and values.
- Paste values only: Paste into cells where only the values are visible.
- Paste formula: Paste into cells where only the formula is visible
- Merge contents: Merge cells and merge all contents together.
- Merge columns: Performs 'Merge contents' for each column in the selected area at once.
- Merge Rows: Performs 'Merge Contents' for each row in the selected area at once.
- The above two functions include maintaining text formatting and removing formatting when merging content.
- Merge Consecutive Values: Automatically merges cells when the same values are consecutive in the column direction (downward).
- Split Row: Splits cell contents with line breaks into multiple rows (lines).
- Split Column: Divide the cell contents with a separator into multiple columns (columns).
- The above two functions can be used with text format or without format.
- Divide and Fill: Separate merged cells and copy all the same content
- Insert Selection: Insert a photo/picture saved on your PC into the selected cell.
- Insert Folder: When a file name is entered in the cell contents, inserts the corresponding photos/pictures from the specified folder in batches.
- Fit Selection: Automatically fit the selected photo/picture to the cell
- Fit All: Automatically fits all photos/pictures in the current sheet to the cells.
- Save All: Save all photos/pictures in the current sheet to the specified folder.
- Create an annual calendar: Insert an annual calendar sheet for the specified year (showing public holidays in Korea)
- Create a monthly schedule: Insert a monthly schedule sheet for the month you specify (showing Korean public holidays and the lunar calendar)
- Create a weekly schedule: Insert a weekly schedule sheet with the dates you specify (showing Korean public holidays, lunar calendar, major events, and time schedules)
- Create a daily schedule: Insert a daily schedule sheet for the specified date (displaying Korean public holidays, lunar calendar, major tasks, time schedule, and work notes)
- Multi-level selection criteria: Create a dropdown list of multi-step validations
- Apply multi-level selection: Apply multi-step validation to selected cells.
- Remove multi-level selection: Remove unnecessary multi-step validation
- UnPivot: De-pivots a table into a normal data type table by de-pivoting it into a Cross Tab or Pivot table.
- Cross Tab: Creates a general data type table as a Cross Tab and aggregates it.
- Table Aggregation: Combine data from multiple sheets of the same format into one.
- Save as Image: Save the selected area as an image file.
- Remove errors: Automatically adds the IFERROR() function to cells that are errors in the current sheet, so that the errors are not visible.
- Remove UDF: If you used UDF of this EG Tools, you can remove UDF and change it to a value when sending it to another PC.
- Delete Style: When there are many cell styles, delete all unused styles or styles that are not built-in.
- Delete Names : Bulk delete invisible named names and invalid names.
- Clean up empty cells: remove zero-length strings from the current sheet
- Trim Ends : Removes spaces from the beginning and end of all cells in the current sheet.
- Rearrange notes: Repositions all notes in the current sheet right next to the inserted cell.
- Mail Merge : Automatically create sheets or files using lists and forms, and print or email them.
- Outline Shapes : Automatically generates free-form shapes along the outlines of blocks drawn in cell background color.
- Fix EGTools Local Link : Modify the path to use EGTools on another PC
- Fix EGTools Array Formula Result : Fix EGTools array and function according to Excel version
- Manual : Shows the documentation for Simple EG Tools
- Version: Shows the current version and shows a link if there is an update released.
- Disable EGTools: Temporarily disable the EGTools add-on, or disable it and delete the files.
We are always grateful to those who help us by providing advice and testing on features and by helping us catch errors.
This file can be used by anyone, including individuals, companies, and government agencies, for free.
All responsibility arising from the use of this file lies with the user.
If you do not agree with this, please stop using it and delete the file.