Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

extract text from a string consiting of mixed charactors and structure

0

I would like to know how to extract/copy an embedded text (ie name) string .... and

place that copied info into another/adjacent column ..... all

while faced with the situation of a 'string' begins &/or ends with numbers, w w/o space, contains symbols, random lengths etc.


for instance chemicals can begin with letters, numbers, etc.. examples follow
all i want to do is extract the 'chemical name' from the text string, in order to Alphabetize/sort or my inventory.  using FIND/SEARCH (left or right, #of charactors) doesnt work since front and  back of the string can vary widely.


1,3-BUTADIENE,  BUTANE FUEL,  JT9458-3/TRICHLOROETHYLENE,  TITANIUM OXIDE TI02,  14735  CARBON POWDER 30 MESH,   etc...

a smaller inventory is not an issue but 1500+ is a bit to manually go through each item and copy/paste the name toa new column

exceltext-manipulation
posted by: jhixy (rep: 2) on Mon Aug 1, 2016 at 1:38 pm
Answer
Discuss

Answers

0

Props for adding a file and including a detailed description! It saves A LOT of back and forth.

What you need to solve this problem is a list of all possible chemical names and then you need a setup that searches the "listed name" column for one of those names and then returns the match in the "extracted info" column.

The reason you need to do it like this is because there is no clear definable pattern for how the names are stored in the original list.

I would create a macro to do this in combination with a list of chemical names somewhere in its own worksheet.

If you need more help, like with the macro, let me know.

posted by: don (rep: 1989) on Mon Aug 1, 2016 at 1:42 pm
Discuss

Discussion

Don, i presumed it would be a rather difficult issue and was hoping not to get into macros ... i have very limited experiance with them.
a list of possible chemical names, that database/spreedsheet would be gi'normous, since our purchase of chemicals is not restricted ... its a get anything you need.

i see your possible macro would also work in other areas ... searching a database wrksht, 'match' and fill value into some cell ... which is how i have done it in the past .. w a <80% accuracy ...
will have to find some other means to followup
jhixy (rep: 2) Aug 1, '16 at 2:01 pm
It can also be done with formulas, but you still need a list of chemical names because there just is no pattern that identifies the names that I am able to see in the data.
don (rep: 1989) Aug 1, '16 at 2:05 pm
DOn,
I only have access to a report that list NOUN and TRADE NAME in the columns...and frequently the 'name' is the same (which is a 'random' mix of alph-num) as example in wrkst.
the ADHOC report is predefined, but i can look to see if the SDS product name is/could be shown in the report...if so problem may be easy to address.
thanks for all your help/input thus far
jhixy (rep: 2) Aug 5, '16 at 11:42 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login