Search and substitute formula

buroh

New Member
Joined
Jul 14, 2020
Messages
43
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
How about
Excel Formula:
=IF($I236="","",SUBSTITUTE($J236,"-"," "))
 
Upvote 0
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.
 
Upvote 0
In that case it would be helpful if you could give a number of samples of your data & the expected result.
 
Upvote 0
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
 
Upvote 0
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," - "," ")," -"," "),"- "," "))
 
Upvote 0
Solution
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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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
Back
Top