Excel Formula to Add a Space to Function Results

Objective

To concatenate a space to the results of a given function in Excel.

Formula Construction

To add a space to the result of a function, you can utilize the & operator or the CONCATENATE function (or its modern equivalent, TEXTJOIN). Below are examples illustrating both approaches.

Method 1: Using the & Operator

The & operator is a straightforward way to append a space to a function's output.

Formula Syntax:

=Function() & " "

Example: To add a space after the result of the UPPER function:

=UPPER(A1) & " "

• Here, A1 contains the text input you wish to convert to uppercase. The formula converts the text in cell A1 to uppercase, and then appends a space after it.

Method 2: Using CONCATENATE (or TEXTJOIN)

CONCATENATE can also be used to achieve similar results, although TEXTJOIN is recommended in newer versions for more flexibility.

Formula Syntax:

=CONCATENATE(Function(), " ")

Or with TEXTJOIN:

=TEXTJOIN("", TRUE, Function(), " ")

Example: Using CONCATENATE with the LOWER function:

=CONCATENATE(LOWER(A1), " ")

Or with TEXTJOIN:

=TEXTJOIN("", TRUE, LOWER(A1), " ")

• In these examples, A1 contains the text input. The LOWER function converts the text to lowercase, and a space is appended afterward.

Explanation of the Formula

• Function(): This is where you insert any Excel function that generates output (like UPPER, LOWER, etc.).
• & " ": The & operator concatenates the output of the function with a space character (" "). This directly appends a space without requiring extra functions.
• CONCATENATE: This function takes multiple arguments and joins them together. In our context, it connects the function result with a space.
• TEXTJOIN: This function allows joining of text items with a specified delimiter, which in this case, effectively helps to join the function result with a space.

Practical Example

Assuming cell A1 contains the text "Hello":

1. Using &:

   • Formula: =UPPER(A1) & " "
   • Result: "HELLO "

2. Using CONCATENATE:

   • Formula: =CONCATENATE(UPPER(A1), " ")
   • Result: "HELLO "

3. Using TEXTJOIN:

   • Formula: =TEXTJOIN("", TRUE, UPPER(A1), " ")
   • Result: "HELLO "

Conclusion

The described methods allow users to append a space to the results of various functions in Excel effectively. Users may choose the method best suited to their implementation preference, noting the & operator is the simplest and most widely used for this purpose. For deeper understanding and further courses on Excel, consider exploring the Enterprise DNA Platform.