Excel function to extract a portion of a string

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.

Who is Participating?
Harry LeeConnect With a Mentor Commented:
Is this what you want?
thutchinsonAuthor Commented:
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.
Harry LeeCommented:
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.
thutchinsonAuthor Commented:
Thanks for the explanation, Harry. Good stuff, good job.  All the best,  --TH
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.