substitute formula I need help with

buroh

New Member
Joined
Jul 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a two columns of text which I need to make sure both match as the data comes from different sources. Both columns of text have characters in the text that can throw off the match formula as being an error, example below:

The name match says no to one of them because there is a - in the document name.
ABCDEF
Sheet NumberSheet NameCurrent Revision
Issued
Name matchDocument numberDocument name
BHE-0001GENERAL NOTES SHEET 01P03yesBHE-0001General Notes - Sheet 01
BHE-0002GENERAL NOTES SHEET 02P03yesBHE-0002General Notes - Sheet 02
BHE-0100SITEWIDE -KEY PLANP03noBHE-0100SiteWide - Key Plan


So I add two hidden columns on the end (G and H) - see below

In Column D, I have the match formula, but still have No error.

In Column G, my formula is =IF($A4="","",SUBSTITUTE(VLOOKUP($A4,$A:$C,2,FALSE)," -"," "))

In Column H, my forumla is =IF($E4="","",SUBSTITUTE(VLOOKUP($E4,$E:$F,2,FALSE)," - "," "))

This works fine, but there examples where the '-' will have a space between that at the next letter; example: SITEWIDE - KEY PLAN, other times it will be as SITEWIDE -KEY PLAN (no space).

So the problem I have is that I want to be able to make my IF formula do carry out the SUBSTITUTE formula based on both conditions and with the end result being this SITEWIDE KEY PLAN so the name match formula end result will say 'Yes'.

There would be text where there is more than one '-' character in the text.

ABCDEFGH
1Sheet NumberSheet NameCurrent Revision
Issued
Name matchDocument numberDocument namerevitissue sheet
2BHE-0001GENERAL NOTES SHEET 01P03yesBHE-0001General Notes - Sheet 01GENERAL NOTES SHEET 01General Notes Sheet 01
3BHE-0002GENERAL NOTES SHEET 02P03yesBHE-0002General Notes - Sheet 02GENERAL NOTES SHEET 02General Notes Sheet 02
4BHE-0100SITEWIDE -KEY PLANP03noBHE-0100SiteWide - Key PlanSITEWIDE KEY PLANSiteWide Key Plan
5BHE-0121STRUCTURAL 3D VIEWS SHEET 01P03yesBHE-0121Structural 3D Views - Sheet 01STRUCTURAL 3D VIEWS SHEET 01Structural 3D Views Sheet 01
6BHE-0122STRUCTURAL 3D VIEWS SHEET 02P03yesBHE-0122Structural 3D Views - Sheet 02STRUCTURAL 3D VIEWS SHEET 02Structural 3D Views Sheet 02

Any help would be great.

The end result would be that the '-' would be omitted and there would be just one normal space between words.

Wayne
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about
Excel Formula:
 =IF($A4="","",trim(SUBSTITUTE(VLOOKUP($A4,$A:$C,2,FALSE),"-"," ")))
 
Upvote 0
you can sub the "-" with space and use the trim() function to delete the extra space like this

Book1
AB
1General Notes - Sheet 01General Notes Sheet 01
2General Notes -Sheet 02General Notes Sheet 02
Sheet1
Cell Formulas
RangeFormula
B1:B2B1=TRIM(SUBSTITUTE(A1,"-",""))
 
Upvote 0
Given that both descriptions have the "-" just in different places I'd be tempted to just substitute all the spaces (ie " ") for "" and then do the match

so it will compare SITEWIDE-KEYPLAN to SiteWide-Key Plan and return a Yes
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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