Excel formula - remove commas, hyphens, spaces and replace with underscore

[mimi_b]

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)

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

Thanks.

 

[Peter_SSs]

Welcome to the MrExcel board!

With Excel 2010, unless you only have a fairly small number of cell values to join, a solution using standard worksheet functions is not very feasible. However, a user-defined function using vba would be relatively simple. Is that acceptable? About how many cell values will need to be joined in that result string?

In any case, before any precise solution can be suggested, you need to clarify a few things please:

• You said that spaces should be replaced by an underscore but your expected output has not done that in the places marked red below.
• Your expected result has also omitted a hyphen between the blue characters, but there was no mention about that in your written description, thought it is in the thread title. Conversely, periods are not mentioned in the title but are in the description.
• With the added text, two of them are "_Text123" and one is "_Text 123". Which is it?

Reyes_MaLuisa_Text123; JunOh_Junee_Text123; Carter_JohnJr_Text 123

 

[Toadstool]

Hi Mimi_b,

Is this what you wanted?

Book1
	A	B
1	Reyes, Ma. Luisa	Reyes_Ma_LuisaText123
2	Jun-Oh, Junee	Jun-Oh_JuneeText123
3	Carter, John Jr.	Carter_John_Jr_Text123
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),".","_"),",","_")," ","_"),"__","_"),"__","_")&"Text123"

 

[alansidman]

An alternative solution is to use Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",",","_",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".","_",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," ","_",Replacer.ReplaceText,{"Column1"}),
    #"Added Suffix" = Table.TransformColumns(#"Replaced Value2", {{"Column1", each _ & "Text123", type text}})
in
    #"Added Suffix"

Book8
	A
1	Column1
2	Reyes__Ma__LuisaText123
3	Jun-Oh__JuneeText123
4	Carter__John_Jr_Text123
Sheet2

 

[mimi_b]

My data consists of Lastname then a comma and space. Firstly I need the last name to not have spaces or other special characters, then an underscore after. So Reyes, becomes Reyes_ and Jun-Oh becomes JunOh_

Next is the given name,no periods or Spaces so Ma. Luisa becomes MaLuisa then add the underscore and additional text. Need to have Reyes_MaLuisa_Text123; JunOh_Junee_Text123; also need cases like Carter, John Jr. to be converted to Carter_JohnJr_Text123

 

[Peter_SSs]

or other special characters

That is pretty vague as it could include a multitude of characters but based on them being periods or hyphens see if this is it

mimi_b 2020-07-04 1.xlsm
	A	B
1	Data	Result
2	Reyes, Ma. Luisa	Reyes_MaLuisa_Text123
3	Jun-Oh, Junee	JunOh_Junee_Text123
4	Carter, John Jr.	Carter_JohnJr_Text123
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","_")," ",""),".",""),"-","")&"_Text123"

 

[mimi_b]

Thank you for providing me with the formula even if my description was vague. The column B results were the output I was looking for.

I was able to try and plug it into my excel sheet and the outcome was what I was expected.

Really appreciate you taking the time and having the patience to see this through.

Regards,
Mimi_b

 

[Peter_SSs]

Glad you got a successful outcome.

I originally thought that you wanted all the names to be combined into a single cell, separated by semicolons which is why I had said it would be difficult with Excel 2010. Fortunately it seems I was wrong about that.