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
105 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
• 13,560 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
• 13,560 points
38 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
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
38 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
32 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
28 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 13,560 points
23 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
• 13,560 points
30 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
• 13,560 points
37 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP