Excel Formula Enhancement

Objective

To modify the existing Excel formula by adding a space after the initials generated by the CONCAT function.

Original Formula

=CONCAT(UPPER(LEFT(AE8, 1)), IFERROR(CONCAT(UPPER(LEFT(MID(AE8, SEARCH(" ", AE8) + 1, LEN(AE8)), 1))), ""))

Modified Formula

To add a space after the initials, you can append " " (a space) at the end of your CONCAT function. Here is the revised formula:

=CONCAT(UPPER(LEFT(AE8, 1)), " ", IFERROR(CONCAT(UPPER(LEFT(MID(AE8, SEARCH(" ", AE8) + 1, LEN(AE8)), 1))), ""))

Explanation of the Formula

• UPPER(LEFT(AE8, 1)): Extracts the first character from cell AE8 and converts it to uppercase.
• SEARCH(" ", AE8) + 1: Finds the position of the first space in AE8 and adds one to get the starting position of the last name or second word.
• MID(AE8, ..., LEN(AE8)): Extracts characters from the string starting from the position calculated previously to the end of the string.
• IFERROR(CONCAT(...), ""): Combines the initials from the second part of the name. If there's an error (for instance, if there is no second name), it returns an empty string.
• " ": This is the addition made to insert a space after the first initialized letter.

Practical Example

Assuming cell AE8 contains the name john doe, using the modified formula:

1. Input in AE8: john doe
2. Output of the formula: J D

The output will show the uppercase initials J and D separated by a space.

Conclusion

The modified formula successfully adds a space after the first initial, making the output cleaner and more readable. For further learning on advanced Excel functions and formulas, consider exploring the Enterprise DNA Platform for comprehensive courses and resources.