substitute formula I need help with

buroh

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

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
 =IF($A4="","",trim(SUBSTITUTE(VLOOKUP($A4,$A:$C,2,FALSE),"-"," ")))
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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,"-",""))
 

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
682
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,334
Messages
5,547,316
Members
410,784
Latest member
apurbakdas
Top