Solved

Excel function to extract a portion of a string

Posted on 2013-11-20
4
788 Views
Last Modified: 2013-11-21
Hi Experts

I need help writing a function that pulls an inventory item number from a product description.

Here's the logic:
If COL C (Item Description) contains a consecutive 5 character numerical string, copy those 5 characters and write them to COL B and make COL D = "Custom".   Otherwise, COL B= "0"& COL A  and COL D= "Co Bill1".
Attached is a file with two tabs.  The tab labeled "Done" shows the correct output from the formula (highlighted in yellow.)

Thanks for your help, experts!  I really appreciate your help while I learn to do this.

-TH
Extract-ItemID-from-product-desc.xlsx
0
Comment
Question by:thutchinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39664637
Is this what you want?
Extract-ItemID-from-product-desc.xlsx
0
 

Author Comment

by:thutchinson
ID: 39665947
That's great, Harry.  

Would you mind explaining the use of the ROW function as an argument to the MID function?  I just can't figure out why this works.  What's with the array $1:$9?

=IF(COUNT(1*MID(C2,ROW($1:$9),1))=5,"Triple S Bill1","Custom")

Thanks so much.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39666900
Well, the Row($1:$9) is pretty much to get an number array of {1,2,3,4,5,6,7,8,9}.

the Mid(C2,{1,2,3,4,5,6,7,8,9},1) basically mean to break up the first 9 characters to and static array. For example if the string is "I am a stupid cat", the array would end up looking like {"I"," ","a","m"," ","a"," ","s","t"}.

Then, 1* will be applied to the {"I"," ","a","m"," ","a"," ","s","t"} individually. Which mean if the characters in the array is not numbers, it will create a #value error.

The function Count only counts numbers but not the #value errors. Which will end up counting how many numeric characters is in the array.

Therefore the formula reads like If there is 5 numeric characters in the first 9 characters in the string, then the result is "Triple S Bill1", otherwise, the result is "Custom".

It's is not exactly what you want. You want 5 consecutive numeric characters in the whole text string but the formula will only test the first 9 characters to see if there is 5 numeric characters. Tell you the truth, I can't quite figure out how to count consecutive numeric characters in text string using formula. In fact, even if I can figure it out in the end, the formula will be ridiculously long.

In VBA, it's much easier for me since I can break up the work by first count the number of characters in the text strings using LEN(text string), then use a loop to break the characters in to a variant array, then count consecutive numeric characters using another loop, and if combination.

Again, even using VBA, it will be a pretty big and slow macro since each product description is going to go through 2 to 3 sets of loops. Which mean if you have total of 100 products, and each product description has about 40 characters long, there will be (100*1 + 100 * 40 + 100 * 40) = 8100 loops to do at least. It's not going to be very nice to run that macro.
0
 

Author Comment

by:thutchinson
ID: 39667150
Thanks for the explanation, Harry. Good stuff, good job.  All the best,  --TH
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question