Delete specific strings on a column with a formula that includes all the strings that i want deleted

U

Uriakus

New Member
Joined
Nov 9, 2014
Messages
13
I'm trying to delete ...

Pantalla 5.5"
Pantalla 5.0"
Pantalla 5.2"
Pantalla 5"
Pantalla 4.0"
Pantalla 2.0"
Pantalla 4.5"
Pantalla 4.7"

from the cellphones names so that they appear as they should ... Acer Liquid Jade Primo 32GB


[TABLE="width: 439"]
<colgroup><col></colgroup><tbody>[TR]
[TD]This is the the column...

Acer Liquid Jade Primo - Pantalla 5.5" - 32GB[/TD]
[/TR]
[TR]
[TD]Acer Liquid Jade Z - Pantalla 5.0" - 1GB - Negro[/TD]
[/TR]
[TR]
[TD]Acer Zest Plus - Pantalla 5.5" - 16GB[/TD]
[/TR]
[TR]
[TD]Asus Zenfone 3 Max - Pantalla 5.2" - Plata[/TD]
[/TR]
[TR]
[TD]Asus Zenfone Go 2 Generación - Pantalla 5" - 8GB - Blanco[/TD]
[/TR]
[TR]
[TD]Asus Zenfone Go 2 Generación - Pantalla 5" - 8GB - Negro[/TD]
[/TR]
[TR]
[TD]Blu Advance L3 - Pantalla 4.0" - 4GB - Gold[/TD]
[/TR]
[TR]
[TD]Blu Advance L3 - Pantalla 4.0" - 4GB - Gold[/TD]
[/TR]
[TR]
[TD]Blu Grand - Pantalla 5.5" - 8GB - Grey[/TD]
[/TR]
[TR]
[TD]Blu Grand - Pantalla 5.5" - 8GB - Grey[/TD]
[/TR]
[TR]
[TD]BLU Grand Max - Pantalla 5" - 8GB - Black[/TD]
[/TR]
[TR]
[TD]BLU Grand Max - Pantalla 5" - 8GB - Gold[/TD]
[/TR]
[TR]
[TD]BLU Grand Max - Pantalla 5" - 8GB - Rose Gold[/TD]
[/TR]
[TR]
[TD]Blu Vivo 5 Mini - Pantalla 4.0" - 8GB - Grey[/TD]
[/TR]
[TR]
[TD]Blu Vivo 5 Mini - Pantalla 4.0" - 8GB - Rose Gold[/TD]
[/TR]
[TR]
[TD]Blu Vivo 5 Mini - Pantalla 4.0" - 8GB - Rose Gold[/TD]
[/TR]
[TR]
[TD]CAT Phone - Pantalla 2.0" - B30[/TD]
[/TR]
[TR]
[TD]CAT Phone - Pantalla 4.5" - 8GB - S30[/TD]
[/TR]
[TR]
[TD]CAT Phone - Pantalla 4.7" - 16GB - S40

I have tried to search without asking but my english is bad and maybe I'm not using the correct words for searching.[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
hola,does this do what you want?


Excel 2013/2016
ABCDEFGHI
1Acer Liquid Jade Primo - Pantalla 5.5" - 32GBAcer Liquid Jade Primo - - 32GBPantalla 5.5"
2Acer Liquid Jade Z - Pantalla 5.0" - 1GB - NegroAcer Liquid Jade Z - - 1GB - NegroPantalla 5.0"
3Acer Zest Plus - Pantalla 5.5" - 16GBAcer Zest Plus - - 16GBPantalla 5.2"
4Asus Zenfone 3 Max - Pantalla 5.2" - PlataAsus Zenfone 3 Max - - PlataPantalla 5"
5Asus Zenfone Go 2 Generacin - Pantalla 5" - 8GB - BlancoAsus Zenfone Go 2 Generacin - - 8GB - BlancoPantalla 4.0"
6Asus Zenfone Go 2 Generacin - Pantalla 5" - 8GB - NegroAsus Zenfone Go 2 Generacin - - 8GB - NegroPantalla 2.0"
7Pantalla 4.5"
8Pantalla 4.7"
Sheet2
Cell Formulas
RangeFormula
B1=SUBSTITUTE(A1,IFERROR(LOOKUP(1E+307,SEARCH($I$1:$I$8,A1),$I$1:$I$8),""),"")
B2=SUBSTITUTE(A2,IFERROR(LOOKUP(1E+307,SEARCH($I$1:$I$8,A2),$I$1:$I$8),""),"")
B3=SUBSTITUTE(A3,IFERROR(LOOKUP(1E+307,SEARCH($I$1:$I$8,A3),$I$1:$I$8),""),"")
B4=SUBSTITUTE(A4,IFERROR(LOOKUP(1E+307,SEARCH($I$1:$I$8,A4),$I$1:$I$8),""),"")
B5=SUBSTITUTE(A5,IFERROR(LOOKUP(1E+307,SEARCH($I$1:$I$8,A5),$I$1:$I$8),""),"")
B6=SUBSTITUTE(A6,IFERROR(LOOKUP(1E+307,SEARCH($I$1:$I$8,A6),$I$1:$I$8),""),"")
 
Upvote 0
Yes. I wanna cry. Thank you.

Is there a way to leave the names like this ...

Acer Liquid Jade Primo 32GB
Acer Liquid Jade Z 1GB
Acer Zest Plus 16GB

I'm trying to do a fuzzy lookup with a table of predetermined names and I have had a lot of problems matching the names with attributes thats why I'm trying to delete as many unnecessary words as possible.

Thank you so much you dont know how much happy I am.
 
Upvote 0
I want to show you the all the data that I'm trying to clean and leave as the real model name.

[TABLE="width: 439"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Model[/TD]
[/TR]
[TR]
[TD]Acer Liquid Jade Primo - Pantalla 5.5" - 32GB[/TD]
[/TR]
[TR]
[TD]Acer Liquid Jade Z - Pantalla 5.0" - 1GB - Negro[/TD]
[/TR]
[TR]
[TD]Acer Zest Plus - Pantalla 5.5" - 16GB[/TD]
[/TR]
[TR]
[TD]Asus Zenfone 3 Max - Pantalla 5.2" - Plata[/TD]
[/TR]
[TR]
[TD]Asus Zenfone Go 2 Generación - Pantalla 5" - 8GB - Blanco[/TD]
[/TR]
[TR]
[TD]Asus Zenfone Go 2 Generación - Pantalla 5" - 8GB - Negro[/TD]
[/TR]
[TR]
[TD]Blu Advance L3 - Pantalla 4.0" - 4GB - Gold[/TD]
[/TR]
[TR]
[TD]Blu Advance L3 - Pantalla 4.0" - 4GB - Gold[/TD]
[/TR]
[TR]
[TD]Blu Grand - Pantalla 5.5" - 8GB - Grey[/TD]
[/TR]
[TR]
[TD]Blu Grand - Pantalla 5.5" - 8GB - Grey[/TD]
[/TR]
[TR]
[TD]BLU Grand Max - Pantalla 5" - 8GB - Black[/TD]
[/TR]
[TR]
[TD]BLU Grand Max - Pantalla 5" - 8GB - Gold[/TD]
[/TR]
[TR]
[TD]BLU Grand Max - Pantalla 5" - 8GB - Rose Gold[/TD]
[/TR]
[TR]
[TD]Blu Vivo 5 Mini - Pantalla 4.0" - 8GB - Grey[/TD]
[/TR]
[TR]
[TD]Blu Vivo 5 Mini - Pantalla 4.0" - 8GB - Rose Gold[/TD]
[/TR]
[TR]
[TD]Blu Vivo 5 Mini - Pantalla 4.0" - 8GB - Rose Gold[/TD]
[/TR]
[TR]
[TD]CAT Phone - Pantalla 2.0" - B30[/TD]
[/TR]
[TR]
[TD]CAT Phone - Pantalla 4.5" - 8GB - S30[/TD]
[/TR]
[TR]
[TD]CAT Phone - Pantalla 4.7" - 16GB - S40[/TD]
[/TR]
[TR]
[TD]CAT Phone - Pantalla 4.7" - 32GB - S60[/TD]
[/TR]
[TR]
[TD]Coolpad Conjr - Pantalla 5.0" - 16GB - Gris Metalizado[/TD]
[/TR]
[TR]
[TD]Coolpad Conjr - Pantalla 5.0" - 16GB - Gris Metalizado[/TD]
[/TR]
[TR]
[TD]Huawei GR5 - Pantalla 5.5” - 16GB - Plateado[/TD]
[/TR]
[TR]
[TD]Huawei Mate 9 - Pantalla 5.9” - 64GB - Gris[/TD]
[/TR]
[TR]
[TD]Huawei Mate 9 - Pantalla 5.9” - 64GB - Plata[/TD]
[/TR]
[TR]
[TD]Huawei Mate 9 - Pantalla 5.9” - 64GB - Plata[/TD]
[/TR]
[TR]
[TD]Huawei Mate 9 - Pantalla 5.9” 64GB - Dorado[/TD]
[/TR]
[TR]
[TD]Huawei Mate 9 - Pantalla 5.9” 64GB - Dorado[/TD]
[/TR]
[TR]
[TD]Huawei P10 - Pantalla 5.1" - 32GB - Gold[/TD]
[/TR]
[TR]
[TD]Huawei P10 - Pantalla 5.1" - 32GB - Gold[/TD]
[/TR]
[TR]
[TD]Huawei P10 - Pantalla 5.1" - 32GB - Silver[/TD]
[/TR]
[TR]
[TD]Huawei P10 - Pantalla 5.1" - 32GB - Silver[/TD]
[/TR]
[TR]
[TD]Huawei Y5 2017 - Pantalla 5" - 16GB - Dorado[/TD]
[/TR]
[TR]
[TD]Huawei Y5 II LTE - Pantalla 5.0” - 8GB - Negro[/TD]
[/TR]
[TR]
[TD]Huawei Y5 Lite - Pantalla 5" - 8GB - Dorado[/TD]
[/TR]
[TR]
[TD]Huawei Y7 - Pantalla 5.5" - 16GB - Dorado[/TD]
[/TR]
[TR]
[TD]Huawei Y7 Prime - Pantalla 5.5" - 32GB - Dorado[/TD]
[/TR]
[TR]
[TD]iPhone 4 Reacondicionado - 8GB - Black[/TD]
[/TR]
[TR]
[TD]iPhone 7 Plus - 32GB - Black[/TD]
[/TR]
[TR]
[TD]iPhone 8 256GB - Gold[/TD]
[/TR]
[TR]
[TD]iPhone 8 256GB - Gold[/TD]
[/TR]
[TR]
[TD]iPhone 8 256GB - Silver[/TD]
[/TR]
[TR]
[TD]iPhone 8 256GB - Space Gray[/TD]
[/TR]
[TR]
[TD]iPhone 8 64GB - Silver[/TD]
[/TR]
[TR]
[TD]iPhone 8 64GB - Silver[/TD]
[/TR]
[TR]
[TD]iPhone 8 64GB - Space Gray[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 256GB - Gold[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 256GB - Gold[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 256GB - Silver[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 256GB - Space Gray[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 256GB - Space Gray[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 64GB - Gold[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 64GB - Silver[/TD]
[/TR]
[TR]
[TD]Iphone SE - 16GB - Space Gray - Apple Certified Pre-Owned[/TD]
[/TR]
[TR]
[TD]iPhone X - Pantalla 5.8" - 256GB - Space Gray[/TD]
[/TR]
[TR]
[TD]iPhone X - Pantalla 5.8" - 256GB - Space Gray[/TD]
[/TR]
[TR]
[TD]iPhone X - Pantalla 5.8" - 64GB - Space Gray[/TD]
[/TR]
[TR]
[TD]iPhone X - Pantalla 5.8" - 64GB - Space Gray[/TD]
[/TR]
[TR]
[TD]LG G6 - Pantalla 5.7" - 32GB - Plata + Accesorios[/TD]
[/TR]
[TR]
[TD]LG K10 2017 - Pantalla 5.3" - 16 GB - Negro[/TD]
[/TR]
[TR]
[TD]LG K10 2017 - Pantalla 5.3" - 16 GB - Negro[/TD]
[/TR]
[TR]
[TD]LG K10 2017 - Pantalla 5.3" - 16 GB - Titan[/TD]
[/TR]
[TR]
[TD]LG K4 - Pantalla 5.0" - 8 GB - Café + Estuche[/TD]
[/TR]
[TR]
[TD]LG K5 - Pantalla 5.0” - 8GB - Negro[/TD]
[/TR]
[TR]
[TD]LG K8 2017 - Pantalla 5” -16GB - Dorado + Estuche[/TD]
[/TR]
[TR]
[TD]LG K8 2017 - Pantalla 5” -16GB - Titan + Estuche[/TD]
[/TR]
[TR]
[TD]LG Q6 Alpha - Pantalla 5.5" - 16GB - Black[/TD]
[/TR]
[TR]
[TD]LG Q6 Alpha - Pantalla 5.5" - 16GB - Ice Platinum[/TD]
[/TR]
[TR]
[TD]LG Q6 Prime - Pantalla 5.5" - 32GB - Ice Platinum[/TD]
[/TR]
[TR]
[TD]LG Q6 Prime - Pantalla 5.5" - 32GB - Ice Platinum[/TD]
[/TR]
[TR]
[TD]LG Q6 Prime - Pantalla 5.5" - 32GB - Pink Gold[/TD]
[/TR]
[TR]
[TD]Maxwest Gravity - Pantala 5.0” - 8GB - Blanco[/TD]
[/TR]
[TR]
[TD]Moto Z2 Play - Pantalla 5.5" - 64GB - Gris[/TD]
[/TR]
[TR]
[TD]Moto Z2 Play - Pantalla 5.5" - 64GB - Gris[/TD]
[/TR]
[TR]
[TD]Motorola Moto C Plus - Pantalla 5" - 16GB - Dorado[/TD]
[/TR]
[TR]
[TD]Motorola Moto E4 Plus - Pantalla 5.5" - Dorado[/TD]
[/TR]
[TR]
[TD]Motorola Moto E4 Plus - Pantalla 5.5" - Dorado[/TD]
[/TR]
[TR]
[TD]Motorola Moto E4 Plus - Pantalla 5.5" - Gris[/TD]
[/TR]
[TR]
[TD]Motorola Moto G5S - Pantalla 5.2" - 32GB - Dorado[/TD]
[/TR]
[TR]
[TD]Motorola Moto G5S - Pantalla 5.2" - 32GB - Dorado[/TD]
[/TR]
[TR]
[TD]Motorola Moto G5S - Pantalla 5.2" - 32GB - Gris[/TD]
[/TR]
[TR]
[TD]Motorola Moto G5S Plus - Pantalla 5.5" - 32GB - Dorado[/TD]
[/TR]
[TR]
[TD]Motorola Moto Z2 Force - Pantalla 5.5" - 64GB - Negro[/TD]
[/TR]
[TR]
[TD]Motorola Moto Z2 Force - Pantalla 5.5" - 64GB - Negro[/TD]
[/TR]
[TR]
[TD]Neffos X1 Lite - Pantalla 5" - 2GB - 16GB - Dorado Metalizado[/TD]
[/TR]
[TR]
[TD]Neffos X1 Lite - Pantalla 5" - 2GB - 16GB - Gris Metalizado[/TD]
[/TR]
[TR]
[TD]Neffos X1 MAX - Pantalla 5.5" - 3GB - 32GB - Gris[/TD]
[/TR]
[TR]
[TD]Neffos Y50 - Pantalla 4.5" - 1GB - 8GB - Blanco[/TD]
[/TR]
[TR]
[TD]Neffos Y50 - Pantalla 4.5" - 1GB - 8GB - Gris[/TD]
[/TR]
[TR]
[TD]Neffos Y5s - Pantalla 5" - 2GB - 16GB - Gris Oscuro[/TD]
[/TR]
[TR]
[TD]Neffos Y5s - Pantalla 5" - 2GB - 16GB - Gris Oscuro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy A5 2017 - 5.2" - 32GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J1 Ace - Pantalla 4.3” - 8GB - Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J1 Ace - Pantalla 4.3” - 8GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J1 Mini Prime - Pantalla 4.0" - 8GB - Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J2 Prime DS - Pantalla 5.0" - 8GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J2 Prime DS - Pantalla 5.0" - 8GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J2 Prime DS - Pantalla 5.0" - 8GB - Plata[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J2 Prime DS - Pantalla 5.0" - 8GB - Plata[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J5 Metal - Pantalla 5.2” - 16GB - Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J5 Metal - Pantalla 5.2” - 16GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J5 Prime - Pantalla 5.0" - 16GB - Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J5 Prime - Pantalla 5.0" - 16GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J5 Prime - Pantalla 5.0" - 16GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Metal - Pantalla 5.5” - 16GB - Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Metal - Pantalla 5.5” - 16GB - Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Metal - Pantalla 5.5” - 16GB - Dorado[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Metal - Pantalla 5.5” - 16GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Dorado + Cover Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Dorado + Cover Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Negro + Cover Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Negro + Cover Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Negro + Cover Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Plata + Cover Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Plata + Cover Blanco[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo - Pantalla 5.5" - 16GB - Plata + Cover Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy Note 8 - 6.3" - 64GB - Dorado[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy Note 8 - 6.3" - 64GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 - Pantalla 5.8" - 64GB - Dorado[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 - Pantalla 5.8" - 64GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 - Pantalla 5.8" - 64GB - Plata[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 - Pantalla 5.8" - 64GB - Plata[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 - Pantalla 5.8" - Gris Orchid[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 Plus - Pantalla 6.2" - 64GB - Dorado[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 Plus - Pantalla 6.2" - 64GB - Dorado[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 Plus - Pantalla 6.2" - 64GB - Gris Orchid[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 Plus - Pantalla 6.2" - 64GB - Gris Orchid[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 Plus - Pantalla 6.2" - 64GB - Negro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 Plus - Pantalla 6.2" - 64GB - Plata[/TD]
[/TR]
[TR]
[TD]Sky Fuego - Pantalla 5.0" - 4GB - Grey[/TD]
[/TR]
[TR]
[TD]Sky Fuego - Pantalla 5.0" - 4GB - Grey[/TD]
[/TR]
[TR]
[TD]Sky Fuego - Pantalla 5.0" - 4GB - White[/TD]
[/TR]
[TR]
[TD]Sky Fuego - Pantalla 5.0" - 4GB - White[/TD]
[/TR]
[TR]
[TD]Sky Platinum - Pantalla 4.0" - 4GB - Gold[/TD]
[/TR]
[TR]
[TD]Sky Platinum - Pantalla 4.0" - 4GB - Grey[/TD]
[/TR]
[TR]
[TD]Sky Platinum - Pantalla 4.0" - 4GB - Rose[/TD]
[/TR]
[TR]
[TD]Sky Platinum - Pantalla 4.0" - 4GB - White[/TD]
[/TR]
[TR]
[TD]Sky Platinum 5.0W - Pantalla 5.0" - 8GB - Blanco[/TD]
[/TR]
[TR]
[TD]Sky Platinum 5.0W - Pantalla 5.0" - 8GB - Gris[/TD]
[/TR]
[TR]
[TD]Sky Platinum 5.5C - Pantalla 5.5" - 8GB - Negro[/TD]
[/TR]
[TR]
[TD]Sony Xperia L1 - Pantalla 5.5" - 16GB - Blanco[/TD]
[/TR]
[TR]
[TD]Sony Xperia L1 - Pantalla 5.5" - 16GB - Negro[/TD]
[/TR]
[TR]
[TD]Sony Xperia XA - Pantalla 5.0" - 16GB

What I'm hoping to obtain is a clean model name without the features.

A few examples ...

Sony XA 16GB
Samsung Galaxy J1 Ace 8GB
iPhone X 256GB
Samsung Galaxy S8 64GB

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
the easiest way without modifying the formula is to add in your search criteria, (column I in my example), a - at the and at the start


Excel 2013/2016
I
1- Pantalla 5.5" -
2- Pantalla 5.0"-
3- Pantalla 5.2"-
4- Pantalla 5"-
5- Pantalla 4.0"-
6- Pantalla 2.0"-
7- Pantalla 4.5"-
8- Pantalla 4.7"-
Sheet1


is this something you can easily do?
 
Upvote 0
I had to changed to spanish but ...

=SUSTITUIR(A1,SI.ERROR(BUSCAR(1E+307,HALLAR($I$1:$I$8,A1),$I$1:$I$8),""),"")

I was not able to extended to the rest of the cells. The Original file is Column A1 to A144 I'm trying to understand what is 1E and 307 in your formula so that i can modify it to my liking
 
Upvote 0
I discover That if i want to extend the range it has to be full with the words that i dont want. but what does this mean in the formula 1E+307
 
Upvote 0
Tell me something... Is there any other way that you concider best for triming all the unwanted words in the list?

What would you do in my case if you had to clean all of those models in the list deleting things such as ... Display (in all of its sizes) and Colors (Dorado, Plata, Negro, Orchid Gray, Etc) to obtain the model name.

This is the reference list...As i want my product to look like.

[TABLE="width: 291"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Acer Liquid Jade Primo[/TD]
[/TR]
[TR]
[TD]Acer Liquid Jade Z[/TD]
[/TR]
[TR]
[TD]Acer Zest Plus[/TD]
[/TR]
[TR]
[TD]Alcatel A3 Plus[/TD]
[/TR]
[TR]
[TD]Alcatel Allura[/TD]
[/TR]
[TR]
[TD]Alcatel Ideal Xcite[/TD]
[/TR]
[TR]
[TD]Alcatel Pixi 3 3.5[/TD]
[/TR]
[TR]
[TD]Alcatel Pixi 4[/TD]
[/TR]
[TR]
[TD]Alcatel Pixi 5[/TD]
[/TR]
[TR]
[TD]Alcatel Pixi 6[/TD]
[/TR]
[TR]
[TD]Alcatel U3[/TD]
[/TR]
[TR]
[TD]Alcatel U5[/TD]
[/TR]
[TR]
[TD]Asus Zenfone 3 Max[/TD]
[/TR]
[TR]
[TD]Asus Zenfone Go 2[/TD]
[/TR]
[TR]
[TD]Blu Advance 4.0 L3[/TD]
[/TR]
[TR]
[TD]Blu Dash G[/TD]
[/TR]
[TR]
[TD]Blu Dash L2[/TD]
[/TR]
[TR]
[TD]Blu Dash L3[/TD]
[/TR]
[TR]
[TD]Blu Dash XL[/TD]
[/TR]
[TR]
[TD]Blu Diva Flex 2.4[/TD]
[/TR]
[TR]
[TD]Blu Grand[/TD]
[/TR]
[TR]
[TD]Blu Grand M[/TD]
[/TR]
[TR]
[TD]BLU Grand Max[/TD]
[/TR]
[TR]
[TD]Blu Grand Mini[/TD]
[/TR]
[TR]
[TD]Blu Life Max[/TD]
[/TR]
[TR]
[TD]Blu Neo X[/TD]
[/TR]
[TR]
[TD]Blu Studio G2[/TD]
[/TR]
[TR]
[TD]Blu Studio G2 HD[/TD]
[/TR]
[TR]
[TD]Blu Studio J1[/TD]
[/TR]
[TR]
[TD]Blu Studio J2[/TD]
[/TR]
[TR]
[TD]Blu Studio J8[/TD]
[/TR]
[TR]
[TD]Blu Studio Touch[/TD]
[/TR]
[TR]
[TD]Blu Studio X8[/TD]
[/TR]
[TR]
[TD]Blu Vivo 5 mini[/TD]
[/TR]
[TR]
[TD]Blu Z3[/TD]
[/TR]
[TR]
[TD]Caterpillar Cat B25[/TD]
[/TR]
[TR]
[TD]Caterpillar Cat B30[/TD]
[/TR]
[TR]
[TD]Caterpillar Cat S30 8GB[/TD]
[/TR]
[TR]
[TD]Caterpillar Cat S40 16GB[/TD]
[/TR]
[TR]
[TD]Caterpillar Cat S60 32GB[/TD]
[/TR]
[TR]
[TD]Coolpad Conjr[/TD]
[/TR]
[TR]
[TD]Doogee X10[/TD]
[/TR]
[TR]
[TD]Doogee X20[/TD]
[/TR]
[TR]
[TD]Doogee X30[/TD]
[/TR]
[TR]
[TD]Doogee X5[/TD]
[/TR]
[TR]
[TD]Doogee X5 Max[/TD]
[/TR]
[TR]
[TD]Huawei G7[/TD]
[/TR]
[TR]
[TD]Huawei GR3[/TD]
[/TR]
[TR]
[TD]Huawei GR5[/TD]
[/TR]
[TR]
[TD]Huawei Mate 7[/TD]
[/TR]
[TR]
[TD]Huawei Mate 8[/TD]
[/TR]
[TR]
[TD]Huawei Mate 9[/TD]
[/TR]
[TR]
[TD]Huawei Mate 9 Lite[/TD]
[/TR]
[TR]
[TD]Huawei Nexus 6P 32GB[/TD]
[/TR]
[TR]
[TD]Huawei Nova[/TD]
[/TR]
[TR]
[TD]Huawei Nova Plus[/TD]
[/TR]
[TR]
[TD]Huawei P10[/TD]
[/TR]
[TR]
[TD]Huawei P10 Lite[/TD]
[/TR]
[TR]
[TD]Huawei P10 Plus[/TD]
[/TR]
[TR]
[TD]Huawei P6[/TD]
[/TR]
[TR]
[TD]Huawei P7 [/TD]
[/TR]
[TR]
[TD]Huawei P8 [/TD]
[/TR]
[TR]
[TD]Huawei P8 Lite[/TD]
[/TR]
[TR]
[TD]Huawei P9 32GB[/TD]
[/TR]
[TR]
[TD]Huawei P9 64GB[/TD]
[/TR]
[TR]
[TD]Huawei P9 Lite[/TD]
[/TR]
[TR]
[TD]Huawei P9 Lite 2017[/TD]
[/TR]
[TR]
[TD]Huawei P9 Lite Mini[/TD]
[/TR]
[TR]
[TD]Huawei P9 Lite Smart[/TD]
[/TR]
[TR]
[TD]Huawei P9 Plus[/TD]
[/TR]
[TR]
[TD]Huawei Y5 2017[/TD]
[/TR]
[TR]
[TD]Huawei Y5 II[/TD]
[/TR]
[TR]
[TD]Huawei Y5 Lite 2017[/TD]
[/TR]
[TR]
[TD]Huawei Y511[/TD]
[/TR]
[TR]
[TD]Huawei Y520[/TD]
[/TR]
[TR]
[TD]Huawei Y530 [/TD]
[/TR]
[TR]
[TD]Huawei Y540[/TD]
[/TR]
[TR]
[TD]Huawei Y550[/TD]
[/TR]
[TR]
[TD]Huawei Y6[/TD]
[/TR]
[TR]
[TD]Huawei Y6 2[/TD]
[/TR]
[TR]
[TD]Huawei Y600[/TD]
[/TR]
[TR]
[TD]Huawei Y625[/TD]
[/TR]
[TR]
[TD]Huawei Y635[/TD]
[/TR]
[TR]
[TD]Huawei Y7[/TD]
[/TR]
[TR]
[TD]Huawei Y7 Prime[/TD]
[/TR]
[TR]
[TD]iPhone 6 32GB [/TD]
[/TR]
[TR]
[TD]iPhone 6 Plus 128GB [/TD]
[/TR]
[TR]
[TD]iPhone 6 Plus 16GB [/TD]
[/TR]
[TR]
[TD]iPhone 6 Plus 64GB [/TD]
[/TR]
[TR]
[TD]iPhone 6S 128GB[/TD]
[/TR]
[TR]
[TD]iPhone 6S 16GB[/TD]
[/TR]
[TR]
[TD]iPhone 6S 32GB[/TD]
[/TR]
[TR]
[TD]iPhone 6S 64GB[/TD]
[/TR]
[TR]
[TD]iPhone 6S Plus 128GB[/TD]
[/TR]
[TR]
[TD]iPhone 6S Plus 16GB[/TD]
[/TR]
[TR]
[TD]iPhone 6S Plus 64GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 128 [/TD]
[/TR]
[TR]
[TD]iPhone 7 128 rojo[/TD]
[/TR]
[TR]
[TD]iPhone 7 256GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 32GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 Jetblack 128GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 Jetblack 256GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 Plus 128GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 Plus 128GB rojo[/TD]
[/TR]
[TR]
[TD]iPhone 7 Plus 256GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 Plus 32GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 Plus Jetblack 128GB[/TD]
[/TR]
[TR]
[TD]iPhone 7 Plus Jetblack 256GB[/TD]
[/TR]
[TR]
[TD]iPhone 8 256GB[/TD]
[/TR]
[TR]
[TD]iPhone 8 64GB[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 256GB[/TD]
[/TR]
[TR]
[TD]iPhone 8 Plus 64GB[/TD]
[/TR]
[TR]
[TD]iPhone SE 16GB[/TD]
[/TR]
[TR]
[TD]iPhone SE 32GB[/TD]
[/TR]
[TR]
[TD]iPhone SE 64GB[/TD]
[/TR]
[TR]
[TD]iPhone X 256GB[/TD]
[/TR]
[TR]
[TD]iPhone X 64GB[/TD]
[/TR]
[TR]
[TD]LG G6[/TD]
[/TR]
[TR]
[TD]LG K10 2017[/TD]
[/TR]
[TR]
[TD]LG K4[/TD]
[/TR]
[TR]
[TD]LG K4 2017[/TD]
[/TR]
[TR]
[TD]LG K5[/TD]
[/TR]
[TR]
[TD]LG K8 2017[/TD]
[/TR]
[TR]
[TD]LG Max[/TD]
[/TR]
[TR]
[TD]LG Phoenix 3[/TD]
[/TR]
[TR]
[TD]LG Q6 Alpha[/TD]
[/TR]
[TR]
[TD]LG Q6 Prime[/TD]
[/TR]
[TR]
[TD]Maxwest Gravity[/TD]
[/TR]
[TR]
[TD]Motorola Moto C 8GB[/TD]
[/TR]
[TR]
[TD]Motorola Moto C Plus[/TD]
[/TR]
[TR]
[TD]Motorola Moto E4 [/TD]
[/TR]
[TR]
[TD]Motorola Moto E4 Plus[/TD]
[/TR]
[TR]
[TD]Motorola Moto G4 16GB[/TD]
[/TR]
[TR]
[TD]Motorola Moto G4 Play 16GB[/TD]
[/TR]
[TR]
[TD]Motorola Moto G4 Plus [/TD]
[/TR]
[TR]
[TD]Motorola Moto G5[/TD]
[/TR]
[TR]
[TD]Motorola Moto G5 Plus[/TD]
[/TR]
[TR]
[TD]Motorola Moto G5S[/TD]
[/TR]
[TR]
[TD]Motorola Moto G5S Plus[/TD]
[/TR]
[TR]
[TD]Motorola Moto Z[/TD]
[/TR]
[TR]
[TD]Motorola Moto Z Play[/TD]
[/TR]
[TR]
[TD]Motorola Moto Z2 Force[/TD]
[/TR]
[TR]
[TD]Motorola Moto Z2 Play[/TD]
[/TR]
[TR]
[TD]Neffos X1 Lite[/TD]
[/TR]
[TR]
[TD]Neffos X1 Max[/TD]
[/TR]
[TR]
[TD]Neffos Y50[/TD]
[/TR]
[TR]
[TD]Neffos Y5s[/TD]
[/TR]
[TR]
[TD]One Plus 5[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy A3 2017[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy A5 2017[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy A7 2017[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy C5[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy C5 Pro 64GB[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy C7[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy C9 Pro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J1 Ace[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J1 Mini Prime[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J2 Prime[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J3 2016[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J5 Metal[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J5 Prime[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J5 Pro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 16GB 4G[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Metal[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Neo[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Prime[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy J7 Pro[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy Note 8[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S7 32GB[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S7 Edge 32GB[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 64GB[/TD]
[/TR]
[TR]
[TD]Samsung Galaxy S8 Plus 64GB[/TD]
[/TR]
[TR]
[TD]Sky Fuego 5.0+[/TD]
[/TR]
[TR]
[TD]Sky Platinum 4.0[/TD]
[/TR]
[TR]
[TD]Sky Platinum 5.0W[/TD]
[/TR]
[TR]
[TD]Sky Platinum 5.5C[/TD]
[/TR]
[TR]
[TD]Sony Xperia L1[/TD]
[/TR]
[TR]
[TD]Sony Xperia M5[/TD]
[/TR]
[TR]
[TD]Sony Xperia X 32GB[/TD]
[/TR]
[TR]
[TD]Sony Xperia X Performance[/TD]
[/TR]
[TR]
[TD]Sony Xperia XA 16GB[/TD]
[/TR]
[TR]
[TD]Sony Xperia XA Ultra[/TD]
[/TR]
[TR]
[TD]Sony Xperia XA1 [/TD]
[/TR]
[TR]
[TD]Sony Xperia XA1 Ultra[/TD]
[/TR]
[TR]
[TD]Sony Xperia XZ[/TD]
[/TR]
[TR]
[TD]Sony Xperia XZ Premium[/TD]
[/TR]
[TR]
[TD]Sony Xperia Z3 Compact[/TD]
[/TR]
[TR]
[TD]Sony Xperia Z3+[/TD]
[/TR]
[TR]
[TD]Sony Xperia Z5[/TD]
[/TR]
[TR]
[TD]Sony Xperia Z5 Compact[/TD]
[/TR]
[TR]
[TD]Sony Xperia Z5 Premium[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi 4A 16GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi 4A 32GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi 4X 32GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi Mi A1 32GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi Mi A1 64GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi Note 4 32GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi Note 4 64GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi Note 5A 16GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi Note 5A 32GB[/TD]
[/TR]
[TR]
[TD]Xiaomi Redmi Note 5A Prime 32GB[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I discover That if i want to extend the range it has to be full with the words that i dont want. but what does this mean in the formula 1E+307
Click to expand...

you do not need to touch the 1e+307 part, it is a constant value used by the lookup formula to do its search


Tell me something... Is there any other way that you concider best for triming all the unwanted words in the list?

What would you do in my case if you had to clean all of those models in the list deleting things such as ... Display (in all of its sizes) and Colors (Dorado, Plata, Negro, Orchid Gray, Etc) to obtain the model name.
Click to expand...

i looked at your example again, and i think you may want to just take anything before the first dash and remove the rest? then it would be even easier
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top