Extract part of data from one cell and insert it in another?

[Lila:-D]

I'm new to Excel so I don't know what kind of key words or phrases would describe the process I'm seeking to use to do the following.

In column 1 of each row I have a randomly structured description of a widget.
Each descriptions includes a string defining one of ten possible model numbers.

I want to search each description for one of the ten possible model numbers and extract just the model number string from each description and place it into an adjacent cell in a newly inserted column 2.

The object is to sort the widgets into model number groups.

Maybe I don't need to extract each model number into a separate cell to do this. It does duplicate information in the table, so perhaps it would be better to search for the model number string in the original description location and sort the data into model number groups from there, if this is possible.
Please advise.

Lila Duncan

[dilipandey]

Hi Lila,

Welcome to the forum.

Please post a sample file for quick try. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Lila:-D]

Hi,
This is a small and simplified example file. I hope it's OK for the purpose and that it's attached properly.


Extract_model_Info_from_A_to_B.xlsx

Lila:-D

[dilipandey]

Hi Lila,

Thanks for the attachment, which is now solved and re-attached.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Lila:-D]

Hi Lila,

Thanks for the attachment, which is now solved and re-attached.

Regards,
DILIPandey

<click on below 'star' if this helps>

Thank you so much for your help DILIPandey. You got Excel to do exactly what I requested. This has taught me something very important, which is not to oversimplify examples. Now I'm embarrassed that your kindly provided solution will not work so well with the more varied structure of the actual and original spreadsheet that the example referred to. Please forgive my silliness, I was trying to be brief. :-/

[Marcol]

To solve your problem you should provide a representitive sample of your data that you need analysed.

This type of question invariably has many anomalies depending on user input, so providing real data is the only way to go.

[dilipandey]

You are welcome Lila...

You explained nicely and offcourse - in details

Feel feel free to reach out in case of more doubts... Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Russell Dawson]

Hi, I'm just an interested party here. How would Lila cope when she tries to sort or filter the data? How can the spaces be ignored or deleted?

[Marcol]

@ DILIPandey

Now I'm embarrassed that your kindly provided solution will not work so well with the more varied structure of the actual and original spreadsheet ...

Did you read that in post #5?
Seems to me the problem still exists! Hence post #6.

[dilipandey]

Hi Marcol,

Thanks for the heads - up.

I had answered / solved the query which Lila had asked in the attachment in post#3... but I already had the hint that problem can be bigger than the attachment and when Lila clarified the same in post#5, I got the point that I was thinking on the right direction and that is why I had written a line "Feel feel free to reach out in case of more doubts... Thanks." in post#7, to which I am awaiting her response. I hope this clarifies my stand. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Lila:-D]

Hi Marcol,

Thanks for the heads - up.

I had answered / solved the query which Lila had asked in the attachment in post#3... but I already had the hint that problem can be bigger than the attachment and when Lila clarified the same in post#5, I got the point that I was thinking on the right direction and that is why I had written a line "Feel feel free to reach out in case of more doubts... Thanks." in post#7, to which I am awaiting her response. I hope this clarifies my stand. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Absolutely so! The main reason that the problem still exists, is that I failed to provide an adequate example, for which I apologize again.
Thanks for your kind offer of further assistance DILIPandey. I'll spend time this weekend preparing a better example, as Marcol suggested.
Thanks all for the suggested solutions to my problem.

[Lila:-D]

Hi again, I oversimplified last time. I hope this time I haven't gone to the other extreme.:-)

I'm helping to provide relatively inexpensive domestic appliances to the long term residents of sheltered housing projects.
These example Excel files show eBay listings for refurbished TVs and Washing machines.
The organization I work for has negotiated with local suppliers of domestic appliances to price match for exact models found on eBay and supply equivalent items free of delivery charge. I'm accumulating spreadsheet records of brands models and prices as a guide and aid to facilitating this process.

For the sake of data uniformity and compactness I want to extract the brand name and model code from the Description column and add each detail to its own dedicated column. My novice understanding of such processes has so far come up with some vague ideas of regex searches of the description fields which then compare with an internalized list of known brands and models, to decide on what to automatically enter into the Brand, model and other possible 'relevant information' columns.

I'm doing this partly for functional practicality purposes and also as an opportunity to learn Excel in depth. I'll be happy to work extremely hard at achieving this if members of this forum will be kind enough to set me on the right track as far as appropriate methods are concerned.

Lila:-D

[Marcol]

You'll need to make lists of keywords for each catagory to be searched.

See the names manager to see how they are dynamically defined, just add to the lists as required.

Note that "Brands" and "Models" are best sorted alphabetically and "OtherInfo" should be arranged in order of priority.
All should be continuous.

The formulae in Sheet "TV" Cells C2:E2 are arrays, and should be confirmed with Ctrl+Shift+Enter not just Enter, then dragged down to suit.

Filter or sort as required.

Hope this gives you a start.

[Lila:-D]

You'll need to make lists of keywords for each catagory to be searched.

See the names manager to see how they are dynamically defined, just add to the lists as required.

Note that "Brands" and "Models" are best sorted alphabetically and "OtherInfo" should be arranged in order of priority.
All should be continuous.

The formulae in Sheet "TV" Cells C2:E2 are arrays, and should be confirmed with Ctrl+Shift+Enter not just Enter, then dragged down to suit.

Filter or sort as required.

Hope this gives you a start.

Thank you so much for this Marcol. Your solution looks great to my inexpert eye, though it will take me a while to step through and understand the method you use. Due to my inexperience I can't yet honestly say whether it's the answer to my problem, though I guess it will be. This being so, I won't devalue the forum's system by clicking the "Solved" button until I've fully understood the effects of your solution. I will be more than glad to click on the star right now, in appreciation of your kindness.