Solved

Extract text from a string

Posted on 2013-11-08
3
319 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:thutchinson
3 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 39633731
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
 
LVL 22

Accepted Solution

by:
rspahitz earned 250 total points
ID: 39634079
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
 

Author Closing Comment

by:thutchinson
ID: 39637073
Both these solutions worked fine. Thanks experts!
0

Featured Post

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

831 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