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]

Cheers. No problem at all. Glad we were able to help with the substitute approach.

 

[Tom.Jones]

Scipio

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

Forgot to change ";" with "," (in my sistem I work with ";" not with ","
Formula will be:
=REGEXREPLACE(A2:A4,{" - "," -","- "},"-")

 

[Peter_SSs]

Formula will be:
=REGEXREPLACE(A2:A4,{" - "," -","- "},"-")

The OP seems satisfied with the substitute approach but to continue the REGEX discussion - that still produces multiple results per data item for me (see below) so I am not sure how that would help to produce a uniformly formatted list of drug names.
(It also no longer deals with the case that there might be multiple spaces like your original suggestion did as shown in row 4).

Scipio.xlsm
	A	B	C	D
1
2	Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML	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	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	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML
Replace (2)

Cell Formulas
Range	Formula
B2:D4	B2	=REGEXREPLACE(A2:A4,{" - "," -","- "},"-")
Dynamic array formulas.

 

[Tom.Jones]

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

Forgot to change ";" with "," (in my sistem I work with ";" not with ","
Formula will be:
=REGEXREPLACE(A2:A4,{" - "," -","- "},"-")

Forgot TRIM in formula

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

 

[Peter_SSs]

@Tom.Jones
Why are you continually failing to to address/comment on the main point of my responses to your posts - the fact that your formula produce 3 results per data item?

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

that still produces multiple results per data item for me (see below) so I am not sure how that would help to produce a uniformly formatted list of drug names.

 

[Tom.Jones]

@Peter_SSs,

You always "correct" some answers... don't you have other things to do?

That's what I understood.

If the data is like this:
Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML

and it is desired like this,

Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML

then the formula:
=REGEXREPLACE(A2:A4,{" - "," -","- "},"-") is OK

If the data is like this:
Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob- ....Xembify Subcutaneous Solution 10 GM/50ML

and it is desired like this:
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML

Then this formula is OK
=REGEXREPLACE(TRIM(A2:A4),{" - "," -","- "},"-").

In the future please refrain from commenting to everyone.

 

[Peter_SSs]

You always "correct" some answers

I did not "correct" you answer.
I asked if you tested it (no response)
I stated that for me it produced more than one result (3 results) for each line of data and asked how that would produce a list as the OP desired.

If the data is like this:
Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML

and it is desired like this,

Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML

then the formula:
=REGEXREPLACE(A2:A4,{" - "," -","- "},"-") is OK

That says that with 3 lines of sample data and 3 lines of desired results that your formula is OK.
My point (& I provided an XL2BB example in post #13 where your formula spills results to cells B2:D4) is that your formula for me produces nine results, not three as in your post above.

If your formula works for you, could you please provide an XL2BB sample, or an uploaded file elsewhere, to demonstrate?
If so, I would then be perfectly happy to accept that for you it works, despite not working for me.

Putting it another way: In post #5 the OP says that the formula in post #3 "worked well". For you, does your formula produce identical results to that formula?

 

[Tom.Jones]

Remove spaces before and after hyphen in text string.xlsx
	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
6
7
8
9
10
11
12
13
14
15
16
17
Foaie1

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

 

[Peter_SSs]

Thanks for the XL2BB sample. I can now see why you consider this formula "working".

However, I do note that ..
A. It is a different formula to the ones you posted previously (the array of string pattern is now a 'vertical' array whereas previously it was a 'horizontal' array), and

B. It does not work in the following common circumstances ...

• if the space & dash arrangement & order in the sample data does not exactly match the space & dash arrangement & order in the formula pattern strings (see rows 7-9 below which have identical data to rows 2:4 just in a different order), or
• if the number of strings in the formula patterns does not match the number of rows of data (rows 12:16 below) which would be a particular problem since the OP has 25,000 rows of data.

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
5
6
7	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML
8	Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML
9	Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML
10
11
12	Imm Glob - Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
13	Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
14	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML	Imm Glob-Xembify Subcutaneous Solution 10 GM/50ML
15	Imm Glob- Xembify Subcutaneous Solution 10 GM/50ML	#N/A
16	Imm Glob -Xembify Subcutaneous Solution 10 GM/50ML	#N/A
Test

Cell Formulas
Range	Formula
B2:B4,B7:B9	B2	=REGEXREPLACE(TRIM(A2:A4),{" - ";" -";"- "},"-")
B12:B16	B12	=REGEXREPLACE(TRIM(A12:A16),{" - ";" -";"- "},"-")
Dynamic array formulas.