Search and substitute formula

[buroh]

Hi all,

I cannot get this formula to work. I tried to using wildcards, but those do not work at all.

So one cell $J236 for example has this text in the cell:

Mechanical Services - HVAC Pipework - Block B First Floor- Strip Out

my formula is at it stands:
=IF($I236="","",IF(ISNUMBER(SEARCH(" - ",$J236)),SUBSTITUTE($J236," - "," "),IF(ISNUMBER(SEARCH(" -",$J236)),SUBSTITUTE($J236," -"," "),IF(ISNUMBER(SEARCH("- ",$J236)),SUBSTITUTE($J236,"- "," "),$J236))))

so the desired end result should be the text about without the - in the whole text.

Before anyone mentions using the replace tool/button on the toolbar, I know that already, but my spreadsheet is set up to eliminate me having to use that function.

the above formula does work to an extent - the result is below:

Mechanical Services HVAC Pipework Block B First Floor- Strip Out

It also works for text in a cell where there is a space before the - followed by text, but it will not do work if the - follows text/number and a space, hence the above issue.

Any help is appreciated.

Wayne

 

[Fluff]

How about

=IF($I236="","",SUBSTITUTE($J236,"-"," "))

 

[buroh]

How about

=IF($I236="","",SUBSTITUTE($J236,"-"," "))

Thanks, but some text in other cells need the - for example Levels 02-10, by omitting the - it will generate an error in match process.

 

[Fluff]

In that case it would be helpful if you could give a number of samples of your data & the expected result.

 

[buroh]

In that case it would be helpful if you could give a number of samples of your data & the expected result.

Okay.

Current text in cells without the formula:

1. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Plan
2. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Sections
3. Mechanical Services - Ventilation - Block B Ground Floor - Strip Out
4. Mechanical Services - Ventilation - Block B First Floor- Strip Out
5. Mechanical Services - Ventilation - Block B Second Floor -Strip Out


Desired result from the formula:

1. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Plan
2. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Sections
3. Mechanical Services Ventilation Block B Ground Floor Strip Out
4. Mechanical Services Ventilation Block B First Floor Strip Out
5. Mechanical Services Ventilation Block B Second Floor Strip Out

As I mentioned in my post, my formula works but only works for 1,2,3 and 5. It does not like no. 4

1,2,3,5 of the desired results is what I get now with my formula, but the end result of No. 4 is shown as: Mechanical Services Ventilation Block B First Floor- Strip Out

I hope that helps.

Wayne

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	I	J	K
1
2	a	1. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Plan	1. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Plan
3	b	2. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Sections	2. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Sections
4	c	3. Mechanical Services - Ventilation - Block B Ground Floor - Strip Out	3. Mechanical Services Ventilation Block B Ground Floor Strip Out
5	d	4. Mechanical Services - Ventilation - Block B First Floor- Strip Out	4. Mechanical Services Ventilation Block B First Floor Strip Out
6	e	5. Mechanical Services - Ventilation - Block B Second Floor -Strip Out	5. Mechanical Services Ventilation Block B Second Floor Strip Out
7
Main

Cell Formulas
Range	Formula
K2:K6	K2	=IF(I2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2," - "," ")," -"," "),"- "," "))

 

[buroh]

Ok, how about

+Fluff v2.xlsm
	I	J	K
1
2	a	1. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Plan	1. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Plan
3	b	2. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Sections	2. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Sections
4	c	3. Mechanical Services - Ventilation - Block B Ground Floor - Strip Out	3. Mechanical Services Ventilation Block B Ground Floor Strip Out
5	d	4. Mechanical Services - Ventilation - Block B First Floor- Strip Out	4. Mechanical Services Ventilation Block B First Floor Strip Out
6	e	5. Mechanical Services - Ventilation - Block B Second Floor -Strip Out	5. Mechanical Services Ventilation Block B Second Floor Strip Out
7
Main

Cell Formulas
Range	Formula
K2:K6	K2	=IF(I2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2," - "," ")," -"," "),"- "," "))

Thank you, that worked.

 

[Fluff]

You're welcome & thanks for the feedback.