MATCH from Range Using Wildcards + MID, FIND

[djmyers]

	COL A	COL B	COL C	COL D
HEADER ROW 1	Project Plan Task	Order #	WORKS	DOESN'T WORK
ROW 2	P90 Text > Category > 19-2494, Project	17-000942	NO	NO
ROW 3	P90 Text > Category > 19-2261, Project	19-002362	NO	NO
ROW 4	P90 RM Text > Category > 18-1524 RM, Project	19-002440	NO	NO
ROW 5		17-000941	NO	NO
ROW 6		19-002494	YES	NO
ROW 7		19-002261	YES	NO
ROW 8		18-001524	YES	NO

In column C, this array formula successfully matches the first 3 characters and last 4 characters of the ORDER # (Col B) the corresponding value found in one of the Project Plan Task
strings in Column A:

Please Login or Register  to view this content.

I have been trying to modify it so that it searches for the truncated version of the ORDER # from the Project Plan Task string within the ORDER # column. This is the formula I wrote, but it doesn't work:

Please Login or Register  to view this content.

What am I missing?

[BMV]

djmyers, Could you attach small example with some real data. If Len of column B is always 9 then REPLACE(b2:b8;4;2 return rhe same as LEFT($B$2:$B$8,3)&RIGHT($B$2:$B$8,4). But its array and its mean The result of match must be aggregate. I suppose

Please Login or Register  to view this content.

[hrlngrv]

You could avoid array formulas and IFERROR in your 1st formula using

C2: =IF(COUNT(1/COUNTIF(A$2:A$8,"* > * > "&LEFT(B2,3)&TEXT(RIGHT(B2,6),"0")&{" *";", *"})),"YES","NO")

The 2nd formula isn't pretty.

D2:
=IF(COUNTIF(B$2:B$8,MID(A2,SEARCH(" > ??-",A2)+3,3)&RIGHT(REPLACE(LEFT(A2,FIND(" ",SUBSTITUTE(A2,","," "),SEARCH(" > ??-",A2)+6)-1),1,
SEARCH(" > ??-",A2)+5,"000000"),6)),"YES","NO")

One of the reasons it isn't pretty is that I'm unsure whether your col A versions of the order numbers drop all zeros immediately after the hyphen. This would be a bit cleaner if you could use 2 cells per result.

E2: =SEARCH(" > ??-",A2)
D2: =IF(COUNTIF(B$2:B$8,MID(A2,E2+3,3)&RIGHT(REPLACE(LEFT(A2,FIND(" ",SUBSTITUTE(A2,","," "),E2+6)-1),1,E2+5,"000000"),6)),"YES","NO")

All this said, you'd be better off using an extra column to reformat the col B order #s in the same format as those in col A.

G2: =LEFT(B2,3)&TEXT(RIGHT(B2,6),"0")

Then the cols C and D formulas could be as simple as [CORRECTION to D2]

C2: =IF(COUNT(1/COUNTIF(A$2:A$8,"* > * > "&G2&{" *";", *"})),"YES","NO")
D2: =IF(COUNT(INDEX(SEARCH("* > * > "&G$2:G$8&{" *",", *"},A2),0,0)),"YES","NO")

[djmyers]

hrlngrv:

Thank you for your suggestions! I really appreciate you coming up with a non-array formula for your Column C suggestion.

Regarding your Column D suggestion (the "not pretty" one), I wonder if you could explain how that one works. When I pull out just a portion of the formula…

=MID(A2,SEARCH(" > ??-",A2)+3,3)&RIGHT(REPLACE(LEFT(A2,FIND(" ",SUBSTITUTE(A2,","," "),SEARCH(" > ??-",A2)+6)-1),1,
SEARCH(" > ??-",A2)+5,"000000"),6)

…The result is: 19-002494

It appears that the leading two 0s from the Order # column are reinserted into the corresponding truncated number from the Project Plan Task string. Is this a fair assumption?

If so, this could be a good thing to do for both columns. The reason: at some point the Order # will turn over from XX-009999 to XX-010000. That point is probably down the road a bit, but it would be prudent for me to anticipate that.

Would you mind explaining how both of your formulas work?

Thank you!

[djmyers]

Hi BMV: For some reason, I can't seem to get files to attach to this forum. Sorry!

[hrlngrv]

. . . Column D suggestion . . .

=MID(A2,SEARCH(" > ??-",A2)+3,3)&RIGHT(REPLACE(LEFT(A2,FIND(" ",SUBSTITUTE(A2,","," "),SEARCH(" > ??-",A2)+6)-1),1,
SEARCH(" > ??-",A2)+5,"000000"),6)

…The result is: 19-002494. . .

Your original col C formula searches for one col B Order # in all entries in col A. Your original col D formula searches for one col A value in all entries in col B. I meant to take the possibly abbreviated order # from the col A value and reformat it as 00-000000 in order search for that in col B.

Is this a fair assumption?

You tell me. You didn't explain what you're trying to do in detail in prose, leaving it up to others to interpret your formula. If my interpretation is wrong, there is a remedy: you could provide more explanation (not formulas) of what you're trying to accomplish in col D.

As for how my formulas work, col C formulas look for one value from col B in col A. LEFT(B2,3)&TEXT(RIGHT(B2,6),"0") takes the 00- part of one col B value and appends TEXT(RIGHT(B2,6),"0"), which is one way of stripping off leading 0s immediately to the right of they hyphen. I could have used --RIGHT(B2,6). At that point, the 2nd argument in the COUNTIF call becomes "* > * > "&(abbreviated order #)&{" *";", *"}, which is an array expression because your sample data had order # immedialy followed by a space and a comma in different cells. Anyway this means starting at the beginning look for any characters then a space-greater then-space, more characters then a space-greater then-space, then the order ID from col B in the same row, then ending either with a space and more characters or a comma, space and more characters. The COUNTIF call would return positive integers when there's a match or 0 when no match. 1/COUNTIF(...) would be a number for positive integers returned by COUNTIF or #DIV/0! for 0 returned by COUNTIF. The COUNT call returns a positive integer if there were any matches or 0 if there were no matches, and nonzero and 0 are treated the same as TRUE and FALSE, respectively, as 1st arguments to IF.

The col D formula extracts the abbreviated order # from one value from col A, reformats the part immediately to the right of the hyphen so that it's 6-digits possibly with leading 0s, then counts the instances of that order # in col B.

I admit that using COUNTIF rather than MATCH is less efficient. COUNTIF goes through its entire 1st argument, MATCH returns after finding the 1st match. OTOH, COUNTIF returns nonnegative integers, and it's easy to distinguish 0 from positive integers. MATCH returns an error, which requires COUNT(MATCH(...)) which returns 1 for a match, 0 for no match.

[djmyers]

hrlngrv: Thank you for your thorough explanation of how the formulas you suggested work. I'll describe below what I'm attempting to accomplish, and apologize in advance if my explanation becomes overly verbose.

The Project Plan Task string is extracted from my organization's ERP (Workday), and the Order # from my department's CRM (Salesforce). Although the CRM originally generates the Order #, someone on the ERP side made the (unwelcome) decision to not only truncate the order number (removing the 1st two digits immediately to the right of the hyphen), but also decided to bury the truncated order number inside a sometimes lengthy text string. At least the structure of that ERP string seems to reliably place the truncated order number to the right of two instances of angle brackets pointing rightward [ > ]. So far, at least… Using an expensive formula to match against that column shouldn't become overly onerous since most of the Project Plan Task datasets to be evaluated will contain less than 1000 records.

My goal is to end up with two reliable formulas: one to match the truncated order number embedded in the Project Plan Task string against its corresponding value in the Order # column, the other to do the reverse — match the value found in the Order # column against its truncated counterpart in the Project Plan Task column. I've tested both of your suggestions against a larger dataset containing the actual data extracts, and both formulas seem to work reliably.

Although I appreciate your earlier suggestion of using a helper column (which I had been doing), I'd like to get away from that because at some point I'll have to hand this task off to a colleague, and I'd prefer to give her/him the most straightforward path for accomplishing it possible.

As for the order number itself, the first two digits to the left of the hyphen (e.g. "17") represents the year the CRM created the order, and the six digits to the right of the hyphen represent the sequence that order came in, no matter the year (e.g. 000503 was the next order to appear after 000502, and came immediately before 000504).

I suppose the "holy grail" of formulas would be those that match the Order #'s first two digits + hyphen — along with its last four digits — to its corresponding value in the Project Plan Task string.

[hrlngrv]

. . .
The Project Plan Task string is extracted from my organization's ERP (Workday), and the Order # from my department's CRM (Salesforce).
. . .
My goal is to end up with two reliable formulas: one to match the truncated order number embedded in the Project Plan Task string against its corresponding value in the Order # column, the other to do the reverse — match the value found in the Order # column against its truncated counterpart in the Project Plan Task column.
. . .

That's what I had inferred from your formulas, and I believe that's what my formulas do.

. . . your earlier suggestion of using a helper column (which I had been doing), I'd like to get away from that . . . the most straightforward path for accomplishing it possible.

If you believe these formulas are straightforward, you're far more generous than I.

Don't count on never using 5 digits after the hyphen in col A. Also, you didn't show any col A values with 3 0s after the hyphen. As long as your col B order #s are always in 00-000000 format, and your col A values use that after removing leading 0s after the hyphen, it's not that difficult.

Adapt the last section of my original response. Take the expression in the G2 formula, and replace instances of G2 with the expression in the C2 and D2 formulas.

C2: =IF(COUNT(1/COUNTIF(A$2:A$8,"* > * > "&LEFT(B2,3)&TEXT(RIGHT(B2,6),"0")&{" *";", *"})),"YES","NO")
D2: =IF(COUNT(INDEX(SEARCH("* > * > "&LEFT(B$2:B$8,3)&TEXT(RIGHT(B$2:B$8,6),"0")&{" *",", *"},A2),0,0)),"YES","NO")

Problem is this D2 formula is VERY INEFFICIENT if you'd have thousands of cells in col B. Much more efficient to parse the order # from col A, and reformat it to 00-000000. Unfortunately, it takes a lot to parse and reformat this in Excel.

D2:
=IF(COUNTIF(B$2:B$8,MID(A2,FIND(CHAR(127),SUBSTITUTE(A2," > ",CHAR(127),2))+3,3)&TEXT(REPLACE(LEFT(A2,
FIND(" ",SUBSTITUTE(A2,","," "),FIND(CHAR(127),SUBSTITUTE(A2," > ",CHAR(127),2))+9)-1),1,
FIND(CHAR(127),SUBSTITUTE(A2," > ",CHAR(127),2))+5,""),"000000")),"YES","NO")

There's no way around those FIND calls if you need to be general and handle any numbers of characters before the 2nd greater than sign and if the abbreviated order # could be immediately followed by either a space or a comma. In short, Excel is a very poor text processing tool, rather like using a saw to drive screws or a screwdriver to cut wood.

If you'd need to hand this off to someone else, I'm not going to suggest VBA.

[BMV]

Hi BMV: For some reason, I can't seem to get files to attach to this forum. Sorry!

It's can be not original file but part of the sheet. Can you imaging for example me should prepare example based on your first post, insert your formulas, correct them because my regional settings different and comma must be replaced to semicolon … .

[djmyers]

hrlngrv: I put together a dataset that, even after fictionalizing the data, more closely represents the actual data I'm working with (see table below).

The Project Plan Task string will almost always contain the Order # in this format: 12-3456.
The actual Order # generated by the CRM will always be in this format: 12-003456.

I've highlighted some anomalies in the data below, like 9998 RM, 20-2737WURSTROK, 9998 Mon Dep, RM 19-2396 and RM 19-2490.

These highlights represent actual inconsistencies in the actual data.

I used a slightly modified formula based on in column C, and your most complex formula for column D.

Col C: =IF(COUNT(1/COUNTIF(A$2:A$44,"* > * > "&LEFT(B2,3)&RIGHT(B2,4)&{" *";", *"})),"YES","NO")

Col D: =IF(COUNTIF(B$2:B$44,MID(A2,FIND(CHAR(127),SUBSTITUTE(A2," > ",CHAR(127),2))+3,3)&TEXT(REPLACE(LEFT(A2,
FIND(" ",SUBSTITUTE(A2,","," "),FIND(CHAR(127),SUBSTITUTE(A2," > ",CHAR(127),2))+9)-1),1,
FIND(CHAR(127),SUBSTITUTE(A2," > ",CHAR(127),2))+5,""),"000000")),"YES","NO")

Other than the strings containing the 9998 values (9998 RM, 9998 Mon Dep — which are just noise), every other row will ideally return a YES in the example shown in the table below since each string contains a truncated version of the corresponding Order #.

I've been trying to adapt a formula that will find the first digit digit hyphen digit digit digit digit pattern in the Project Plan Task string and compare that digits-hyphen-digits pattern to its closest counterpart in the Order # field (ignoring that field's first two digits to the right of the hyphen).

So far, everything I've tried in that regard has failed.

Your thoughts?

	A	B	C	D
1	Project Plan Task	Order #	djmyers Modification of hrlngrv Col C Formula	hrlngrv Complex Col D Formula
2	P90 Part Proj - Part > Part > 16-0259, 8805 Accent Bridge	16-000259	YES	YES
3	P90 Part Proj - Part > Part > 16-0260, 8806 Alpine Entertainment	16-000260	YES	YES
4	P90 Part Proj - Part > Part > 17-0336, 8815 Banshee	17-000336	YES	YES
5	P90 Part Proj - Part > Part > 17-0890, 8653 Blue Intelligence	17-000890	YES	YES
6	P90 Part Proj - Part > Part > 17-1084, 9022 07-130 Cavern Poly	17-001084	YES	YES
7	P90 Part Proj - Part > Part > 18-1909, 9392 2018-126 Cloud Entertainment	18-001909	YES	YES
8	P90 Part Proj - Part > Part > 19-2494 18-1434 Crow Corporation	19-002494	YES	YES
9	P90 Part Proj - Part > Part > 19-2596 7947 Dino Nite	19-002596	YES	YES
10	P90 Part Proj - Part > Part > 20-2689 5101 Gnome Man	20-002689	YES	YES
11	P90 Part Proj - Part > Part > 20-2709 2019-124 Grasshopper Limited	20-002709	YES	YES
12	P90 RM Dep Proj Mon Dep Track > Sub-Div > 16-0066 RM, 8565 Griff Industries	16-000066	YES	YES
13	P90 RM Dep Proj Mon Dep Track > Sub-Div > 16-0139 RM, 8579 Hatch Scape	16-000139	YES	YES
14	P90 RM Dep Proj Mon Dep Track > Sub-Div > 17-1028 RM, 9044 PL1311-Heartelligence 006-009	17-001028	YES	YES
15	P90 RM Dep Proj Mon Dep Track > Sub-Div > 18-1484 RM, 7407 PL1291-100-102 Hero Softwares	18-001484	YES	YES
16	P90 RM Dep Proj Mon Dep Track > Sub-Div > 18-1485 RM, 7391 2 - PL1259-084-086 Hummingbirdustries	18-001485	YES	YES
17	P90 RM Dep Proj Mon Dep Track > Sub-Div > 19-2045 RM Karma Bank	19-002045	YES	YES
18	P90 RM Dep Proj Mon Dep Track > Sub-Div > 9998 RM Mon Dep Unclaimed	9998 RM M	NO	NO
19	P90 Sub-Div Proj - Sub > Sub-Div > 16-0028, 8707 Sullivans Gulch Karmarts	16-000028	YES	YES
20	P90 Sub-Div Proj - Sub > Sub-Div > 16-0033, 8583 Lioness Electronics	16-000033	YES	YES
21	P90 Sub-Div Proj - Sub > Sub-Div > 16-0066, 8565 Meadows Lioness Wheels	16-000066	YES	YES
22	P90 Sub-Div Proj - Sub > Sub-Div > 16-0139, 8579 Melonetworks	16-000139	YES	YES
23	P90 Sub-Div Proj - Sub > Sub-Div > 16-0176, 8843 One Mermedia	16-000176	YES	YES
24	P90 Sub-Div Proj - Sub > Sub-Div > 16-0305, 8849 Neroductions RIDGE PL1311-048-049	16-000305	YES	YES
25	P90 Sub-Div Proj - Sub > Sub-Div > 17-0677, 8954 Night Electronics - PL1311-092-093	17-000677	YES	YES
26	P90 Sub-Div Proj - Sub > Sub-Div > 17-0678, 8955 Orangations - PL1312-012	17-000678	YES	YES
27	P90 Sub-Div Proj - Sub > Sub-Div > 17-0801, 9043 BIG OAK AT BROOKSIDE - PL1311-094-095 Padlock Enterprises	17-000801	YES	YES
28	P90 Sub-Div Proj - Sub > Sub-Div > 17-0966, 9035 MACADAM Pixy Walk	17-000966	YES	YES
29	P90 Sub-Div Proj - Sub > Sub-Div > 17-1080, 9049 Prime Well - PL1311-023-028	17-001080	YES	YES
30	P90 Sub-Div Proj - Sub > Sub-Div > 18-1222, 9113 BARNEY'S CROSSING	18-001222	YES	YES
31	P90 Sub-Div Proj - Sub > Sub-Div > 18-1407, 7429 DRIFTWOOD MEADOWS Quad Paw	18-001407	YES	YES
32	P90 Sub-Div Proj - Sub > Sub-Div > 18-1501, 8041 KNIGHT MEADOWS - PL1307-063	18-001501	YES	YES
33	P90 Sub-Div Proj - Sub > Sub-Div > 18-1620, 9196 River Air CONDOMINIUM	18-001620	YES	YES
34	P90 Sub-Div Proj - Sub > Sub-Div > 18-1648, 7934 Robin Hut RENAISSANCE 28 - PL1305-028-029	18-001648	YES	YES
35	P90 Sub-Div Proj - Sub > Sub-Div > 18-1649 4262 Place Robin Wood	18-001649	YES	YES
36	P90 Sub-Div Proj - Sub > Sub-Div > 18-1911, 9422 DIRTEE STREET CONDOMINIUMS	18-001911	YES	YES
37	P90 Sub-Div Proj - Sub > Sub-Div > 19-2419 Sail Bit CONDOMINIUM	19-002419	YES	YES
38	P90 Sub-Div Proj - Sub > Sub-Div > 19-2421 KRACKER PARK CONDOMINIUMS	19-002421	YES	YES
39	P90 Sub-Div Proj - Sub > Sub-Div > 19-2501 FORTY-THREE THIRTY-TWO SE MADMAN CONDO	19-002501	YES	YES
40	P90 Sub-Div Proj - Sub > Sub-Div > 20-2710 SAINT ROSE PETALS	20-002710	YES	YES
41	P90 Sub-Division Survey - Subdivision > Sub-Division > 20-2737WURSTROK VILLAS	20-002737	NO	NO
42	P90 Sub-Div Proj - Sub > Sub-Div > 9998 Mon Dep Unclaimed	9998 Mon	NO	NO
43	P90 Sub-Div Proj - Sub > Sub-Div > RM 19-2396 VENAL PLACE PRODUCTIONS	19-002396	NO	NO
44	P90 Sub-Div Proj - Sub > Sub-Div > RM 19-2490 BLAND VISTA TOWER	19-002490	NO	NO

[djmyers]

BMV: I put up a Google Sheet with the most recent table shown on this tread here:
https://docs.google.com/spreadsheets...it?usp=sharing

Hope this helps!

[BMV]

I" m not sure I understand aim but

Please Login or Register  to view this content.

[Phuocam]

Maybe ?

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$2:$B$44,SUBSTITUTE(A2,"-","-00"))))>0,"YES","NO")

[hrlngrv]

See this linked workbook on OneDrive.

I had to change the col C formulas to ensure order #s have 4 digits to the right of the hyphen when applicable.

See the cols E and F formulas. They work with your sample data, but they make more assumptions. For col E (replacement for col C), that order numbers in col A are either 00-0000 or 0000. For col F (replacement for col D), that all characters to the right of the 2nd greater than sign in col A values other than the 1st batch of digits and hyphens can be ignored.

I also added regular expression formulas in cols E and F in your Google Sheets workbook. Even with regexes, the formulas a quite long. That's a polite way of saying your data is in serious need of cleansing at the upstream systems. IMO, there's no excuse for the 20-2737WURSTROK anomaly other than the programmers who generated it just don't give a @@@@ about how much unnecessary work it generates downstream.

[djmyers]

You've done amazing work, hrlngrv.

I am finding an anomaly in the "Column D" formula that I wonder if you would mind taking a look at. I've spent considerable time trying to figure it out, but I'm getting nowhere. I've replicated this anomaly (which I first discovered when I was using your Column D formula in the actual data) in a slightly fictionalized version of the string. The issue persists even after the string was fictionalized.

You can take a look at it here: MATCH from Range Using Wildcards + MID, FIND - FORMULA TESTER

Thank you again for all of your amazing work on this question.

[hrlngrv]

The false YES values in G2 and G3 are due to something I had tried to protect against in previous replies. Specifically, the 2nd argument to COUNTIF returns 16-*0005* for both of those cells. That happens to match 16-000054 in cell D22. Looks like it's necessary to convert col A order #s like 00-0000 to 00-000000. Hopefully the 0000 order #s in A45 and A46 only need to be matched as-is.

I think I editied your workbook on OneDrive, but I only added cells/formulas from col J right. You can see how I break down the problem. Cols J to M highlight the false matches. Col N is the 1st digit to the right of the 2nd greater than sign in the col A value. Col 0 is the number of digit or hyphen characters from the col N position right until the first non-digit/non-hyphen character. Col P just comfirms those positions by extracting the col A order #. Col Q looks for a hyphen in the col P value/col A order #, and if found, takes the 1st 2 digits and hyphen as-is, then converts the digits after the hyphen to 6 digits with leading zeros as needed; otherwise (no hyphen), it takes the string of digits as-is. Col R uses the col Q value as 2nd argument to COUNTIF. Col S uses that COUNTIF result to determine whether to return YES or NO. Col T started off as the col S formula, then I replaced the col Q reference with the col Q formula, then I replaced the col N and col O references with the col N and col O formulas.

The result is unpleasant.

T2:
=IF(COUNTIF($D$2:$D$44,IF(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",FIND(CHAR(127),SUBSTITUTE(A2,">",CHAR(127),2))))+2,1)="-",
MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",FIND(CHAR(127),SUBSTITUTE(A2,">",CHAR(127),2)))),3)
&TEXT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",FIND(CHAR(127),SUBSTITUTE(A2,">",CHAR(127),2))))+3,
MATCH(TRUE,ISERROR(FIND(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",FIND(CHAR(127),
SUBSTITUTE(A2,">",CHAR(127),2))))+{1,2,3,4,5,6,7,8,9},1),"-0123456789")),0)-3),"000000"),MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},
A2&"0123456789",FIND(CHAR(127),SUBSTITUTE(A2,">",CHAR(127),2)))),MATCH(TRUE,ISERROR(FIND(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},
A2&"0123456789",FIND(CHAR(127),SUBSTITUTE(A2,">",CHAR(127),2))))+{1,2,3,4,5,6,7,8,9},1),"-0123456789")),0)))),"YES","NO")

Do you really believe anyone to whom you'd pass off the workbook you're building would want to maintain this formula rather than the cols N, O, and Q formulas with the col S formula moved to col G?

I could even anticipate more problems if there were any actual col A values with a hyphen immediately after a digit followed by a non-digit, e.g., foo > bar > 99-8765-oops. The col Q formula would give 99-8765-. Sure, the formula could be made even longer to handle that, but I'd argue this has gone well past the point at which a single cell formula makes sense. In any case, I added cols V to Y that handle any non-digit following the string of digits following the hyphen (if any) after the 2nd greater than sign in col A values.

If there could be other anomalies, you may need to consider VBA.

[djmyers]

Thank you for this suggestion, BMV. This formula seems to work in the example data, as you have demonstrated with your attached spreadsheet. For some reason that I'm struggling to understand, this formula does not seem to work in the actual data.

I'm not sure I understand aim but…

The aim is to evaluate if single Project Plan Task string contains a truncated version of the order number that most closely matches the actual order number somewhere in Order # field. If it does, return a "YES" -- if not, a "NO". Does that make sense?

Can you describe how your formula works? Thanks!

[djmyers]

Thank you for posting this, Phuocam. This formula gives me a "YES" response to both YES and NO data when I test it in the actual data.

[djmyers]

Thank you, hrlngrv, for your very thorough explanation. I think you've won this debate; you're making a believer out of me in that I couldn't hand such a formula over to one of my coworkers, unless I wanted to take advantage of this state's assisted suicide laws, and I wanted that coworker to do the "assist."

That said, someone posted a couple of formulas on the Google Sheet (columns E & F) that I want to take a closer look at, possibly this weekend. I can't seem to get these formulas to work in Excel, but want to test them using the actual data in the Google Sheets environment. I'd do it this evening, but it's already been a brutal work week, even though it's only halfway through (factoring in the holiday this past Monday), so I'm taking a break instead.

Thank you again!

[hrlngrv]

. . . someone posted a couple of formulas on the Google Sheet (columns E & F) that I want to take a closer look at . . .

Maybe someone else did, but I did as well. Mine used Google Sheets's regexreplace() function. That doesn't exist in Excel, and likely won't before 2021, all of which may be irrelevant if you and your coworker are still using Excel 2010. OTOH, you could use VBA to implement user-defined function wrappers around the Windows Script Host DLL's RegEx object.

[djmyers]

Maybe someone else did, but I did as well. Mine used Google Sheets's regexreplace() function.

So sorry for my forgetfulness, hrlngrv. Maybe someday you'll reach my age, then you'll know what it's like to live in a continuous "senior moment." But I'm glad you reminded me; it turns out that our organization bought into the G Suite environment, which means our email is Gmail instead of Outlook, etc. Of course, we also have ready access to all the Docs apps. So I'll just try to get buy-in from my counterpart in the other department to port this whole project to Google Sheets. If he balks, I'll just brandish your Excel formula for Col D…

Thanks again!

[hrlngrv]

. . .someday you'll reach my age, . . .

I ain't that young myself. I have 3 kids, all now college graduates.

. . . also have ready access to all the Docs apps . . . port this whole project to Google Sheets. . . .

At the very least, you should be able to make the point that Google Sheets would be far superior to Excel for data cleansing.

Alternatively, there's using additional columns per col A value to isolate the order # and reformat it as 00-000000. With enough comments and warnings, whoever gets this project from you should be able to see there's a real need to break out the col A order #, and it's just not sensible to try to do so in a single formula without regular expression functions.

That said, my Google Sheets formulas may fail for the latest anomalies just like my monster Excel formulas for the same reason: the col D one produces 00-*0000* order #s from col A. I've added those anomaly records to your Google Sheets workbook in rows 51 down, and I've come up with an alternative way to parse and reformat col B-format order #s from col A order #s in cols G to I. Same logic as the biggest monster Excel formula: look for 1st string of digits after the 2nd greater than sign, extract 2 digits followed by a hyphen if such pattern exists, then pull all digits. Reformat -0000 as -000000 when there's a hyphen, otherwise leave that 1st digit string as-is.