How extract brands from text?
Hi, I have an excel file with 400k rows and I need to extract brands from text, I'm a beginner at excel, maybe someone can suggest something?
I have attached an image where the first column is current text and the second column how it should be
Screenshot 2022-09-27 at 11.57.37.png
Example file
example.xlsm
Last edited by dev_ed; 09-27-2022 at 05:07 AM.
DO you have a definitive list of suppliers within your workbook?
If someone has helped you then please add to their Reputation
No, I don't. I have only this column where is a brand with a product name, but I don't need a product name
So the issue you have is what defines a brand? We can look at PEUGEOT, APPLE etc and recognise them as brands but Excel has no concept of that, your data has nothing specific to identify a brand. You can't say "always take the first x words" because you have examples of both 1 and 2 word brand names. I initially thought, ok the brands are in capitals however rows 6 and 11 dispute that.
You can try Power Query with Column From Examples
Edit:
btw. first try to remove duplicates, will be less rows
Now Result Now DB Nakr?tka koA?a M18x1,5 pasuje do: ME DB DB Nakr?tka koA?a M18x1,5 pasuje do: ME DB A?ruba szeA?ciok?tna hex M6/16mm pas DB DB A?ruba szeA?ciok?tna hex M6/16mm pas DB A?ruba prowadnicy zacisku hamulcowego DB DB A?ruba prowadnicy zacisku hamulcowego DB Nakr?tka mocowania resora M16 pasuje DB DB Nakr?tka mocowania resora M16 pasuje INTER CARS KUBEK CERAMICZNY Q-service Pr INTER CARS INTER CARS KUBEK CERAMICZNY Q-service Pr SOLARIS Mocowanie/chwyt por?czy (pA?etw SOLARIS SOLARIS Mocowanie/chwyt por?czy (pA?etw PEUGEOT Poduszka osA?ony silnika gA3ra p PEUGEOT PEUGEOT Poduszka osA?ony silnika gA3ra p PEUGEOT Uszczelka przewodu smarowania tu PEUGEOT PEUGEOT Uszczelka przewodu smarowania tu IMNASA Manetka IMNASA IMNASA Manetka INTER CARS FLAGA QS-MOTO 3X1 INTER CARS INTER CARS FLAGA QS-MOTO 3X1 DB Filtr oleju pasuje do: MERCEDES DB DB Filtr oleju pasuje do: MERCEDES BPART Mata filtracyjna(380x310) BPART BPART Mata filtracyjna(380x310) SOLARIS Uchwyt specjalny SOLARIS SOLARIS Uchwyt specjalny OEM Ci?gno dr?A1ka zmiany biegA3w (skr OEM OEM Ci?gno dr?A1ka zmiany biegA3w (skr MAGNETI MARELLI PrzeA??cznik zespolony MAGNETI MARELLI MAGNETI MARELLI PrzeA??cznik zespolony MAGNETI MARELLI PrzeA??cznik zespolony MAGNETI MARELLI 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 4 RIDE 4MAX A??cznik rur (42x42x125mm) pasuje 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 4 RIDE 4MAX A??cznik rur (42x42x125mm, opakowa 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 4 RIDE 3K Turbospr?A1arka (Nowy) 4MAX A??cznik rur (42x42x125mm) pasuje 4MAX 4MAX A??cznik rur (42x42x125mm, opakowa 4MAX 3K Turbospr?A1arka (Nowy) 3K 3K Turbospr?A1arka (Nowy) 3K 3K Turbospr?A1arka (Nowy) 3K
Last edited by sandy666; 09-27-2022 at 05:40 AM.
I can't because there are other data, like product code, price, etc...Originally Posted by sandy666
different code, price etc. for the same thing from the first column ???
This is a complete file of how it looks now
Attachment 798011
try attach file again (there is no file)
Sorry, try now
this is not 400k rows
btw.
here are duplicates then you can check values in other columns
Now MAGNETI MARELLI PrzeA??cznik zespolony MAGNETI MARELLI PrzeA??cznik zespolony 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 3K Turbospr?A1arka (Nowy) 3K Turbospr?A1arka (Nowy) 3K Turbospr?A1arka (Nowy)
ok , in the second file are distinct values
but in the first are duplicates
so where is the truth ?
I can't add full file, because the size is over 30 MB and zip file is 18 MB
Here is the full file on sendspacethis is not 400k rows
btw.
here are duplicates then you can check values in other columns
Now MAGNETI MARELLI PrzeA??cznik zespolony MAGNETI MARELLI PrzeA??cznik zespolony 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 4 RIDE Zestaw A?oA1ysk koA?a z uszczelni 3K Turbospr?A1arka (Nowy) 3K Turbospr?A1arka (Nowy) 3K Turbospr?A1arka (Nowy)
full file size is 18 MB not 30
sendspace.com/file/upmb2q
this is not a problemHere is the full file on sendspace
full file size is 18 MB not 30
is that what you want?
full size file xlsx download
Last edited by sandy666; 09-27-2022 at 08:19 AM.
[QUOTE=sandy666;5732767]is that what you want?
can't open the full file link, there are 25Mb limitation
try this here
Another approach - based on your earlier small sample file.
It assumes a maximum of 2 words per brand and will only work on a PC (not Mac).
=FILTERXML("<A><B>"&SUBSTITUTE(B16," ","</B><B>")&"</B></A>","//B[1]")&IFERROR(" "&FILTERXML("<A><B>"&SUBSTITUTE(B16," ","</B><B>")&"</B></A>","//B[2][translate(.,'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ')=.]"),"")
Use the formula in the file, as this one above may have had some symbols changed by the EF platform.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Yes, this is exactly what I needed, how did you do that?
I used Power Query
Can you explain more? I don't know anything about Power Query, you use any function, formula, or what? I want to learn, so I can do it by myself
Data tab
Show Queries & Connections
double click on the Table you'll see on the right side
so there are steps where you can see HowTo
also you can open Advanced Editor from Power Query Editor window (Home tab)
more about Power Query: https://learn.microsoft.com/en-us/power-query/
Click the Add Reputation at the foot of any of the posts of members who helped you
Last edited by sandy666; 09-27-2022 at 04:02 PM.
Did you even LOOK at Post 19?
Any sort of response would be polite.
@Glenn
see this (result of your formula without duplicates)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks