Solved

Excel function to extract a portion of a string

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

734 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