Solved

Excel function to extract a portion of a string

Posted on 2013-11-20
4
748 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
  • 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now