📊 Curso Google Sheets ✅ Funciones de texto, SPLIT, CONCATENAR, TEXTJOIN, EXTRAE, etc.

Google Sheets Functions: Working with Text Strings

Introduction to Google Sheets Text Functions

• The video focuses on functions in Google Sheets, specifically those that manipulate text strings.

• The presenter opens a spreadsheet containing two sheets: one for mathematical functions and another for text functions.

Using the SPLIT Function

• The first function discussed is SPLIT, which separates a string based on a specified delimiter.

• An example is provided where the presenter references cell A1 (containing their name) to demonstrate how to use the SPLIT function.

• The delimiter used in this case is a space, allowing separation of the first name from last names.

• When executed, SPLIT places each part of the name into separate cells based on spaces found in the original string.

• This function can be dragged down to apply it to multiple rows efficiently.

Understanding CONCATENATE Function

• Next, the video introduces the CONCATENATE function, which combines content from multiple cells into one cell.

• The presenter demonstrates how to concatenate manually entered strings or cell references while emphasizing that spaces must also be included as delimiters within quotes.

• An example shows concatenating three cells without spaces initially; adjustments are made by adding spaces between elements using double quotes and semicolons.

• It’s highlighted that every segment added requires proper spacing for clarity in output results when concatenating names and surnames together.

Advanced Concatenation Techniques

• Further customization allows rearranging components like placing surnames before names with appropriate punctuation (commas and spaces).

• The presenter illustrates how to format outputs correctly by ensuring all necessary delimiters are included for clear presentation of names.

Introduction to TEXTJOIN Function

Concatenating Cell Contents in Excel

Introduction to Concatenation

• The process of concatenating cell contents involves using a delimiter, which is the character that separates the content from different cells.

• The first step is to define the delimiter; for example, a space or a hyphen can be used to separate names like "duardo" and "torregrosa".

Setting Parameters for Concatenation

• When setting up the function, parameters include defining whether empty cells should be included in the result. Setting this parameter to TRUE excludes empty cells.

• Selecting multiple cells for concatenation will yield results based on the defined delimiter; changing it from a space to a hyphen alters how results are displayed.

Understanding TRUE and FALSE Parameters

• If set to FALSE, including an additional empty cell will add an extra delimiter (e.g., hyphen), demonstrating how parameters affect output.

• Adjusting back to TRUE removes unnecessary delimiters when there are empty cells between filled ones.

Using Text Functions: LEFT and RIGHT

LEFT Function Overview

• The LEFT function allows users to extract characters from the left side of a string. It requires specifying both the text string and the number of characters desired.

• For instance, entering 3 retrieves "Edu" from "Eduardo", while entering 6 would return "Eduard".

RIGHT Function Overview

• Similarly, the RIGHT function extracts characters starting from the right end of a string. Specifying 3 returns "C" if applied on "Eduardo".

Extracting Substrings with MID Function

MID Function Usage

• The MID function enables extraction of characters starting at any specified position within a string. This is useful for isolating parts of text such as surnames.

• To extract a surname like “Torregrosa”, you would specify where it starts (character 9 in this case).

Replacing Text with SUBSTITUTE Function

SUBSTITUTE Function Mechanics

• The SUBSTITUTE function replaces occurrences of specific text within a string with new text. It requires four parameters: original text, search text, replacement text, and occurrence number.

• For example, replacing all instances of “a” in “banana” with “ae” demonstrates how this function modifies strings effectively.

Understanding String Replacement Functions in Programming

Function Parameters and Default Behavior

• The use of semicolons in function parameters highlights the current parameter, which is crucial for understanding how string replacement works.

• When a value (e.g., 1, 2, or 3) is assigned to the parameter, it specifies which occurrence of the target character will be replaced. For instance, setting it to 3 replaces only the third occurrence.

• If no parameter is provided, all occurrences of the target character are replaced by default. This behavior can lead to unexpected results if not properly managed.

Practical Examples of String Replacement

• Assigning a value greater than the number of occurrences (e.g., 5 when there are only four 'A's) results in no changes being made to the string.

• Demonstrating with an example where modifying a string after replacing characters shows how functions interact with variable content.

Comparing Strings: Equality Functions

Basic Comparison Mechanics

• The equality function compares two strings and returns true or false based on their content. For example, comparing "V4" with "V4" yields true.

• Manual input can also be used for comparisons; changing one character leads to a false result when compared against an identical string.

Advanced Comparison Techniques

• The comparison function can handle both cell references and manually entered strings, allowing flexibility in testing equality across different data types.

Text Case Conversion Functions

Uppercase and Lowercase Transformations

• Functions that convert text to uppercase or lowercase are straightforward; they can either reference specific cells or accept manual input for transformation.

Counting Cells with Content

Functions of Text in Excel

Overview of Useful Text Functions

• The video discusses several text functions in Excel, emphasizing the most commonly used and useful ones. It suggests that viewers can explore additional text functions by navigating to the relevant section within the software.

• Examples of other interesting functions include one that removes blank spaces and another that capitalizes the first letter of each word in a name.

Understanding Function Parameters

• As users progress through the course, they will see brief descriptions of each function's purpose. Clicking on a function provides a more detailed explanation of its application.

• The interface highlights parameters in green as they are being used, helping users identify which parameters are currently active.