Remove spaces before and after hyphen in text string

[Scipio]

Consider the text string:

Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML

This appears as a drug name in a cell in Excel.

I need a formula to remove the spaces before and after the hyphen that I can then copy down about 25,000 rows.

I have tried various forms of IF(ISNUMBER(SEARCH combined with concatenating with TEXTAFTER and/or TEXTBEFORE but cannot come up with a way to remove both the spaces.

Objective:

Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML

Problem: same drug name appears in the database with space before the hyphen, no space before the hyphen, no space before the hyphen and space after...you get the picture --- totally inconsistent naming conventions.

It is wreaking havoc on my data analysis as my pivot table cannot properly group all records of the same drug due to the inconsistent use of text and spaces in the naming conventions.

Would be grateful for any suggestions.

Kind Regards,
Scipio

 

[Peter_SSs]

Do you need a formula copied down all those rows? Could you just use Excel's Find/Replace to replace " -" with "-" and then Find/Replace to replace "- " with "-" in the original column?

Otherwise, if you have the new REGEX functions you could try this

25 03 08.xlsm
	A	B
1
2	Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
3	Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
4	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
5
Replace

Cell Formulas
Range	Formula
B2:B4	B2	=REGEXREPLACE(A2:A4," *\- *","-")
Dynamic array formulas.

 

[Peter_SSs]

.. or if you don't have REGEX

25 03 08.xlsm
	A	B
1
2	Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
3	Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
4	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
5
Replace

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(SUBSTITUTE(TRIM(A2:A4)," -","-"),"- ","-")
Dynamic array formulas.

 

[Scipio]

Thanks Peter. I do need to copy down all the rows. I will give the new REGEX function a look.
Thanks again.

 

[Scipio]

Peter --- thank you. The SUBSTITUTE + TRIM Functions worked well. I'm going to study the new REGEX functions when time permits, but in the meantime, your suggestion has helped tremendously. Thanks again.

Kind Regards,
Scipio

 

[Peter_SSs]

You’re welcome. Thanks for the follow-up.

 

[Tom.Jones]

Scipio

I'm going to study the new REGEX functions when

=REGEXREPLACE(TRIM(A2:A4);{" - "," -","- "},"-")

 

[Scipio]

Looks straightforward enough. Thank you.

Scipio

 

[Peter_SSs]

Scipio

=REGEXREPLACE(TRIM(A2:A4);{" - "," -","- "},"-")

Did you try that? For me (once the formula separators were made consistent) that produced three results for each item of sample data.


@Scipio
If you have looked at or are going to look at the REGEX options I did have an extra character in my post 2 suggestion. The extra character makes no difference to the working of the formula but is not necessary. The adjusted formula would be

Scipio.xlsm
	A	B
1
2	Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
3	Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
4	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Replace

Cell Formulas
Range	Formula
B2:B4	B2	=REGEXREPLACE(A2:A4," *- *","-")
Dynamic array formulas.

 

[Scipio]

Peter --- thank you. I haven't had time to look at it yet but will study your formula when time permits. Thank you again for the help with the Substitute function --- it made a huge difference in our ability to transform our raw data into the analysis we need. For many Excel users like me, text formulas often present complexities that can be more challenging than numerical calculations, so I really appreciate the help.