Search and substitute formula

buroh

New Member
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,984
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF($I236="","",SUBSTITUTE($J236,"-"," "))
 

buroh

New Member
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,984
Office Version
  1. 365
Platform
  1. Windows
In that case it would be helpful if you could give a number of samples of your data & the expected result.
 

buroh

New Member
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,984
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff v2.xlsm
IJK
1
2a1. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Plan1. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Plan
3b2. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Sections2. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Sections
4c3. Mechanical Services - Ventilation - Block B Ground Floor - Strip Out3. Mechanical Services Ventilation Block B Ground Floor Strip Out
5d4. Mechanical Services - Ventilation - Block B First Floor- Strip Out4. Mechanical Services Ventilation Block B First Floor Strip Out
6e5. Mechanical Services - Ventilation - Block B Second Floor -Strip Out5. Mechanical Services Ventilation Block B Second Floor Strip Out
7
Main
Cell Formulas
RangeFormula
K2:K6K2=IF(I2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2," - "," ")," -"," "),"- "," "))
 
Solution

buroh

New Member
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff v2.xlsm
IJK
1
2a1. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Plan1. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Plan
3b2. Typical Room Detail - Combined Services - Level B2 Chilled Water Plant-Room Sections2. Typical Room Detail Combined Services Level B2 Chilled Water Plant-Room Sections
4c3. Mechanical Services - Ventilation - Block B Ground Floor - Strip Out3. Mechanical Services Ventilation Block B Ground Floor Strip Out
5d4. Mechanical Services - Ventilation - Block B First Floor- Strip Out4. Mechanical Services Ventilation Block B First Floor Strip Out
6e5. Mechanical Services - Ventilation - Block B Second Floor -Strip Out5. Mechanical Services Ventilation Block B Second Floor Strip Out
7
Main
Cell Formulas
RangeFormula
K2:K6K2=IF(I2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2," - "," ")," -"," "),"- "," "))
Thank you, that worked.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,984
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,369
Messages
5,641,737
Members
417,233
Latest member
MakeSense

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top