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.

-TH
Extract-ItemID-from-product-desc.xlsx
thutchinsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:
Is this what you want?
Extract-ItemID-from-product-desc.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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.
0
thutchinsonAuthor Commented:
Thanks for the explanation, Harry. Good stuff, good job.  All the best,  --TH
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.