Excel Tips: Cleaning up data with REGEX functions скачать в хорошем качестве

Excel Tips: Cleaning up data with REGEX functions

Learn how to use Excel’s regular expressions (REGEX) functionality to help you clean up messy data. Read more of Neale’s Excel articles on INTHEBLACK: https://intheblack.cpaaustralia.com.a... 00:23 Three new functions that use Regular Expressions – abbreviated to REGEX functions. 00:46 Structured text works will with three other functions TEXTBEFORE, TEXTAFTER and TEXTSPLIT. 01:21 REGEX patterns are deigned to work with unstructured text. 01:34 Example of extracting email addresses from sentences using REGEXEXTRACT. 02:31 REGEX patterns need to be enclosed in quotation marks. 02:47 Two other REGEX functions are REGEXREPLACE and REGEXTEST. 03:07 A tip to copy from within the Formula Bar to stop relative references changing. 03:26 REGEXREPLACE example. 04:48 Codes to refer to different types of characters in REGEX patterns. 05:59 Whitespace definition and usage. 06:28 Codes for Quantifiers – these allow you specify how many of certain characters. 07:28 Carat symbol ^ (above 6 on the keyboard) used to identify the start of a string and $ identifies the end of a string. 07:48 Artificial Intelligence systems can create REGEX patterns and there are a lot of resources online for REGEX patterns. 08:09 Examples of common patterns. 08:28 The pattern for the first entry of something will be much shorter than the pattern for the last entry. 08:46 Numbers other than whole numbers can require complex patterns. 09:51 REGEX functions default to case sensitive, but the setting can be changed. 10:48 More examples of different first and last entries. 11:05 Words can include numbers. 11:59 Hyphen are not included in standard words. You need to include it separately if you need it. 12:24 Whole numbers are relatively easy to work with. 13:55 Two functions have been updated to use REGEX patterns. XLOOKUP and XMATCH. 15:05 REGEXTEST function example with the FILTER function. Pattern placed in a cell to make it easier to use. 16:56 The HYPERLINK function can create a clickable link to create an email. #exceltips #exceltutorials #regex how to use REGEX in Excel REGEXEXTRACT example Excel using REGEX with XLOOKUP clean up messy data in Excel three new Excel functions based on regular expressions (REGEX)