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
• 9,320 points
47 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 Edureka
• 8,820 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 Edureka
• 8,820 points
29 views
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
• 9,320 points
32 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
• 9,320 points
16 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
• 9,320 points
21 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 Edureka
• 8,820 points
17 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 Edureka
• 8,820 points
20 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 Edureka
• 8,820 points
22 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