Excel formula to remove comma spaces period and add a text

Question

Context

I have data like this:

Reyes, Ma. Luisa Jun-Oh, Junee Carter, John Jr.

I need to use a combination of excel formulas in one cell to remove commas, periods, spaces and replace with underscore. Also need to add text after the names (to be used as PDF filename).

Expected output

Reyes_MaLuisa_Text123; JunOh_Junee_Text123; Carter_JohnJr_Text 123 (no period and trailing space removed)

What I've tried

Formula used: =REPLACE(I2,FIND(", ",I2,1),2,"_") & "_Text123" where I is the name field.

Thanks.

Answer 1

The steps to accomplish this are as follows:

The dataset should be chosen (the one with the numbers)
On the ribbon, click the Home tab.
Select the dialogue box launcher from the numbers group (the small slanted arrow icon at the bottom-right part of the group)
Check that the Number tab is selected in the Format Cells dialogue box.
Select Number from the drop-down menu (if not selected already)
Uncheck the box that says "Use 1000 Separator (,)" and enter the number of decimal digits you want.
Press the OK button.