Extract text from a string

The product item number (ItemNo) that I need is buried in the product description (Description) field.


See sample file attached. Is there a way to run a macro or a procedure to extract the item number from COL and write it to Col B?

The item number is always at the end of the string but not all products have an item number. To demonstrate, I completed through row 8 manually.
Extract-ItemID-from-product-desc.xlsx
thutchinsonAsked:
Who is Participating?
 
rspahitzConnect With a Mentor Commented:
Here's another formula to stick in column B (after you change the datatype back to General):

=IF(ISNUMBER(VALUE(RIGHT(A2,1))),TRIM(RIGHT(A2,6)),"")

Open in new window


This assumes that the the numbers you seek are all 6 characters or less.
0
 
barry houdiniConnect With a Mentor Commented:
You can do this with a formula

Select B2 and format as general then paste this formula in B2

=IF(ISERR(RIGHT(A2)+0),"",TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,")"," ")," ",REPT(" ",99)),99)))

drag formula down column......or just put cursor on bottom corner of B2 until you see a black "+" (the "fill-handle") now double click and the formula will populate all the way down automatically - see attached

regards, barry
Extract-ItemID-barry.xlsx
0
 
thutchinsonAuthor Commented:
Both these solutions worked fine. Thanks experts!
0
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.