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, 2022 in Database by Edureka
• 13,670 points
1,886 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, 2022 by gaurav
• 23,260 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, 2022 in Database by gaurav
• 23,260 points
597 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, 2022 in Others by narikkadan
• 63,700 points
310 views
0 votes
1 answer

How to remove data within a multi-line cell that comes after a few line breaks

find() return the starting position of a ...READ MORE

answered Jan 26, 2023 in Others by narikkadan
• 63,700 points
568 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, 2022 in Others by Edureka
• 13,670 points
1,718 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, 2022 in Others by Edureka
• 13,670 points
1,560 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, 2022 in Database by gaurav
• 23,260 points
728 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, 2022 in Database by gaurav
• 23,260 points
11,655 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