Unable to Nest IFERROR for Multiple Wildcards Patterns

M

markgro

New Member
Joined
Jun 23, 2014
Messages
4
I am trying to create a function that will truncate a string. I am using 64-bit MS Excel 2013 Pro. This function will be used with over 6,000 cells each containing lots of data.

I am truncating from the left in a non-case sensitive manner:
Cell C2 =IFERROR(RIGHT(B2,LEN(B2)-SEARCH({"weight:*grams"},B2)+1),"")

Followed truncating from the right also in a non-case sensitive manner:
Cell D2 =IFERROR(LEFT(C2,SEARCH({"gram"},C2)+4),"")

(I'd like to later nest these two functions for simplicity.)

This pair of functions yields results such as:
Weight: 4.2 grams
Weight: 33.7 grams
Weight: 20 grams
Weight: 6.00 grams

But due to the earlier occurrence of "Weight:" in many entries this approach will also yield:
WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 grams
...and similar results that haven't been adequately truncated on the left.

For the first equation, I've tried to use the ? wildcard instead of * by:
1) adding additional case values within the brackets ( { } )
2) nesting IFERROR functions

But ...

the first approach is yielding FALSE if the first case value is not true, regardless of the validity of the subsequent cases

and ...

the second approach is yielding #Value! errors if the first case is not true, regardless of the validity of the subsequent cases.

This would be a powerful data mining tool for folks to add to their collection if only I could make a little more robust. Any ideas?
 

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.
Sort by date Sort by votes
So far my workaround looks like this. I have a spreadsheet for each characteristic (e.g., weight, height, width, thickness, length, total CTW) with input and output consolidate in columns on a preceding spreadsheet with values further concatenated to remove the units. I've ordered the operations so that the most frequently encountered number of "?" wildcards appears to the left for each characteristic. I alternate between the boolean operations to improve my chances of catching an error and to minimize resource demands.

<colgroup><col></colgroup><tbody>
</tbody>
Cell C2: =IFERROR(RIGHT(B2,LEN(B2)-SEARCH({"weight: ??? grams"},B2)+1),FALSE)
Cell D2: =IF(C2=FALSE,IFERROR(RIGHT(B2,LEN(B2)-SEARCH({"weight: ???? grams"},B2)+1),TRUE),IF(C2<>"",C2,""))
Cell E2: =IF(D2=TRUE,IFERROR(RIGHT(B2,LEN(B2)-SEARCH({"weight: ?? grams"},B2)+1),FALSE),IF(D2<>"",D2,""))
Cell F2: =IF(E2=FALSE,IFERROR(RIGHT(B2,LEN(B2)-SEARCH({"weight: ????? grams"},B2)+1),TRUE),IF(E2<>"",E2,""))
Cell G2: =IF(F2=TRUE,IFERROR(RIGHT(B2,LEN(B2)-SEARCH({"weight: ? grams"},B2)+1),FALSE),IF(F2<>"",F2,""))
Cell H2: =IFERROR(LEFT(G2,SEARCH({"gram"},G2)+4),"")

But is there a simpler way? I'd like to condense the six columns of wildcard operations on each operation tab into a single column on the input / output tab. Also, as I work through the characteristics for the approximately 6,050 rows, I may encounter additional wildcard scenarios (e.g., weights in ounces instead of grams) requiring even more columns.

Mark Grohman, PMP, LSSBB, MCP
 
Upvote 0
markgro said:
I am trying to create a function that will truncate a string. I am using 64-bit MS Excel 2013 Pro. This function will be used with over 6,000 cells each containing lots of data.

I am truncating from the left in a non-case sensitive manner:
Cell C2 =IFERROR(RIGHT(B2,LEN(B2)-SEARCH({"weight:*grams"},B2)+1),"")

Followed truncating from the right also in a non-case sensitive manner:
Cell D2 =IFERROR(LEFT(C2,SEARCH({"gram"},C2)+4),"")

(I'd like to later nest these two functions for simplicity.)

This pair of functions yields results such as:
Weight: 4.2 grams
Weight: 33.7 grams
Weight: 20 grams
Weight: 6.00 grams

But due to the earlier occurrence of "Weight:" in many entries this approach will also yield:
WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 grams
...and similar results that haven't been adequately truncated on the left.

For the first equation, I've tried to use the ? wildcard instead of * by:
1) adding additional case values within the brackets ( { } )
2) nesting IFERROR functions

But ...

the first approach is yielding FALSE if the first case value is not true, regardless of the validity of the subsequent cases

and ...

the second approach is yielding #Value! errors if the first case is not true, regardless of the validity of the subsequent cases.

This would be a powerful data mining tool for folks to add to their collection if only I could make a little more robust. Any ideas?
Click to expand...
Formula is in E2<html><head><title>Excel Jeanie HTML</title></head><body>
<!-- ######### Start Created Html Code To Copy ########## -->
Excel Workbook
BCDE
1LONG STRINGTRUNCATE LEFTTRUNCATE RIGHTFormula
2WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsdddWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsdddWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 1.9 grams
3WEIGHT: 0.05 WEIGHT: 1.9 gramsWEIGHT: 0.05 WEIGHT: 1.9 gramsWEIGHT: 0.05 WEIGHT: 1.9 gramsWEIGHT: 1.9 grams
4WEIGHT: 0.0IGHT: 1.9 gramsWEIGHT: 0.0IGHT: 1.9 gramsWEIGHT: 0.0IGHT: 1.9 gramsWEIGHT: 0.0IGHT: 1.9 grams
5WEIGHT: 0.05   
6WEIGHT: 1.9 gramsWEIGHT: 1.9 gramsWEIGHT: 1.9 gramsWEIGHT: 1.9 grams
7a WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 1.9 grams
8WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 1.9 grams
9WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 1.9 grams
10WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 1.9 grams
11WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 1.9 grams
12WEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 0.05 CTW RING SIZE: 7 WEIGHT: 1.9 gramsWEIGHT: 1.9 grams
Sheet1

?
?
<!-- ######### End Created Html Code To Copy ########## -->
</body></html>
 
Upvote 0
Hi,

Would it be possible to have some sample data together with your expected results?

Regards
 
Upvote 0
Pattern recognition for an engineering data set in which there is more than one occurrence within the long text string for the word “WEIGHT” and we only need to extract data from the second occurrence to the word “grams” included
Notes: Hi Mark, This formula only considers the case in which there are a max of two occurrences. Validate your data to be sure that this is your situation and also be sure that it is the second occurrence the one that contains the pertinent data. ( Validate this fact against the ERP system)
I hope it helps.
Mario

 
Upvote 0
LOL, I am creating the ERP system out of eBay using an unsupported feature in TurboLister with all titles made unique so that I can then find the Item IDs from a different listing report using VLookUp functions. Source data such as the two examples below serve as our current ERP.

I need a little more time to evaluate your approach as I am now back in the office after working virtual since Friday. It does look like it will save a few columns with the possible risk of being tricked by a few possible source strings. Thanks!

Any other ideas out there?

Mark Grohman, PMP, LSSBB, MCP

MEGA JEWELRY SALE! Pure Swarovski Crystals Encased in Rose Gold Thread Gold Finish Journey Necklace Made of 100% Pure Austrian Swarovski Element Austrian Swarovski Crystals have an air of understated elegance. These crystals will surely take her breath away in just a glance. Let a reminder of your love adorn her delicate neck wherever she goes. These brilliant white Swarovski crystals fill the net design crafted with pieces textured and polished 18K Gold thread. Fall in love with this Swarovski crystal bracelet. A stunning oval shaped charm adorn the two stylish uneven layers of crystal in encased 18K White Gold thread. Celebrate an anniversary, birthday, holiday, or even "just because" to show how your love for her grows day by day with this sparkling three segment journey bracelet. Shimmering Perfection! Are you looking for the perfect gift for that special someone in your life? Whether it's your daughter, sister, wife or mother, this darling necklace will certainly put a smile on her face. Unlike inferior products, this bracelet is dipped in Pure 18K Gold. It's exclusively cut Swarovski Crystals will shimmer and sparkle till the end of time. These brilliant white Swarovski crystals fill the net design crafted with pieces textured and polished 18K Gold thread. A lobster clasp clips to any link on the extension for a comfortable and secure fit. Our precious collection of Swarovski Crystal jewelry will definitely compliment any outfit, any day. This is the perfect way to say "I Love You!" Don't Hesitate! Show appreciation to your loved one today! Dimension Length: 18 inches Weight: 9.4 grams Metal: Pure 18K White Gold Finish over Genuine Sterling Silver ***FREE SHIPPING*** IN ORDER TO SHOW APPRECIATION TO OUR CUSTOMERS, WE WILL SHIP THEM IN A GIFT BOX AT NO EXTRA COST SO YOU CAN SURPRISE YOUR LOVED ONES!! WE LOVE OUR CUSTOMERS AND LOOK FORWARD TO MAKING THEM HAPPY!

HTML Generator Sample Page Womens 3/4 CT Diamond 14k Gold Finnish Wedding Ring Anniversary Wedding Band ***MICRO PAVE SETTING BAND*** ITEM DIMENSIONS: TOTAL BLACK DIAMOND WEIGHT: 0.75 CT RING SIZE: 7 ESTIMATED WEIGHT: 9.00 grams HEIGHT: 25 mm WIDTH OF TOP SETTING: 28.00 mm METAL: 14K YELLOW GOLD OVER SOLID STERLING SILVER QUALITY OF DIAMONDS: I1 COLOR OF DIAMONDS: G-H ITEM NUMBER: RNG003389 SIZING All our rings can be sized. Please message us to find out details for sizing. Most rings can be sized for an addition $39.99. However, if there is more gold needed or diamonds to be added the cost will vary. Re-sized items can-not be returned. SUGGESTED RETAIL PRICE: $899.00 OUR PRICE: $299.00 YOU SAVE: 66% Top Quality Jewelry at a Bargain Price
 
Upvote 0
You must log in or register to reply here.

Similar threads

DrMrsStark
  • Question
Formula to search range by two criteria or different two criteria, and if not found then search different range using same criteria(s)
Replies
5
Views
2K
jasonb75
J
R
  • Question
VBA Automation Error - possibly born from my complete lack of understanding with Error Handling
2 3
Replies
23
Views
7K
RockandGrohl
R
C
  • Question
Combining Multiple Individual Functions Into One
Replies
9
Views
988
jtakw
jtakw
B
  • Question
MedianIfs
Replies
9
Views
9K
Aladin Akyurek
Aladin Akyurek
N
  • Question
Truncate text strings using LEFT and FIND/SEARCH for multiple search terms
Replies
5
Views
1K
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,214,957
Messages
6,122,466
Members
449,086
Latest member
kwindels

Share this page

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