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