Excel formula to remove comma spaces period and add a text

0 votes

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.

Mar 11 in Database by Edureka
• 13,640 points
677 views

1 answer to this question.

0 votes
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.
answered Mar 15 by gaurav
• 22,040 points

Related Questions In Database

0 votes
1 answer

Excel formula to remove space between words in a cell

There are three fast techniques to get ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
123 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Concatenate columns using LEFT and RIGHT in EXCEL

It's conceivable that the Concat/Concatenate function isn't ...READ MORE

answered Nov 25 in Others by narikkadan
• 37,660 points
20 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
143 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
245 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
110 views
0 votes
1 answer

Excel TEXT formula doesn't convert 'yyyy' to a year

It appears that if you have a ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
91 views
0 votes
1 answer

What is the Excel formula to countif text partially makes up a cell?

With Formulas, Countif Partial String/Substring Match We can ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
1,845 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP