How Can I remove certain DATA

G

Gmata

i have Over 3000 rows of data like this:

Alain Delon 1.7 oz Eau De Toilette 15.05
Jil Sander 2.5 oz Eau De Toilette Spray 19.55
Cartier 1 oz Eau De Toilette Spray 21.70
Cartier 3.3 oz Eau De Toilette Spray 47.46
Cartier 3.4 oz Eau De Toilette Concentree Spray 48.38
Pal Zileri 3.4 oz Eau De Toilette Spray 29.38
Jessica Mcclintock 3.4 oz After Shave Soother 20.77
Nikos 3.4 oz Eau De Toilette Spray 19.55
Sergio Tacchini 3.3 oz Eau De Toilette Spray 16.78

I need to remove the Brand (i.e. Alain Delon) and the Price (last 4 digits
i.e. 15.05)
from all the rows

Any Suggestions?

Thanks
 
T

Tyro

What do you mean by "remove"? Replace Alain Delon and the price "15.05" with
spaces or remove any row that contains Alain Delon and the price of "15.05"
or what? It helps if you are specific in your question.

Tyro
 
G

Gmata

Tyro said:
What do you mean by "remove"? Replace Alain Delon and the price "15.05" with
spaces or remove any row that contains Alain Delon and the price of "15.05"
or what? It helps if you are specific in your question.

Tyro




I Need to Remove the BRAND AND THE PRICE from every row, i have over 3000 rows and all the information is in the same Column.
Click to expand...
Does that make sense now?
 
D

Dave Peterson

With your data in A1:A3000
I'd put this array formula in B1:
=MIN(MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

It'll tell you the position of the first number in the string.

Then I'd use this in C1:
=TRIM(MID(A1,B1,LEN(A1)-B1-5+1))

And select B1:C1 and drag down as far as you need.
 
R

Ron Rosenfeld

i have Over 3000 rows of data like this:

Alain Delon 1.7 oz Eau De Toilette 15.05
Jil Sander 2.5 oz Eau De Toilette Spray 19.55
Cartier 1 oz Eau De Toilette Spray 21.70
Cartier 3.3 oz Eau De Toilette Spray 47.46
Cartier 3.4 oz Eau De Toilette Concentree Spray 48.38
Pal Zileri 3.4 oz Eau De Toilette Spray 29.38
Jessica Mcclintock 3.4 oz After Shave Soother 20.77
Nikos 3.4 oz Eau De Toilette Spray 19.55
Sergio Tacchini 3.3 oz Eau De Toilette Spray 16.78

I need to remove the Brand (i.e. Alain Delon) and the Price (last 4 digits
i.e. 15.05)
from all the rows

Any Suggestions?

Thanks
Click to expand...

I'm not sure exactly what you mean, but this UDF will return the line without
the Brand and the Price.

It does depend on a <space> and a number after the brand (and no separate
number within the brand)

Given your data, it will return:

1.7 oz Eau De Toilette
2.5 oz Eau De Toilette Spray
1 oz Eau De Toilette Spray
3.3 oz Eau De Toilette Spray
3.4 oz Eau De Toilette Concentree Spray
3.4 oz Eau De Toilette Spray
3.4 oz After Shave Soother
3.4 oz Eau De Toilette Spray
3.3 oz Eau De Toilette Spray

To enter the code, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, with data in A1, enter

B1: =RemBrandPrice(A1)

and fill down as far a required.

=============================
Option Explicit
Function RemBrandPrice(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "^(\D+)\s+(\b\d*\.?\d+\b.*?)\s+(\b\d*\.?\d+\b)"
RemBrandPrice = re.Replace(str, "$2")
End Function
================================

If this is not how you want this process to work, post more specific request.

A function is "dynamic". But you could also have a macro that just removes the
Brand and Price in the original cells, as well as other approaches.
--ron
 
G

Gmata

Thanks Ron for your Help.

However i am not able to make it work, i did everything you told me to do
and when i put the formula: =RemBrandPrice(A1) the result is the exact same
information i have in column A1, it dosen't remove the Brand or Price at the
end. I tried this with all the 3900 products and it didn't do anything. What
am i doing wrong?
 
R

Ron Rosenfeld

Thanks Ron for your Help.

However i am not able to make it work, i did everything you told me to do
and when i put the formula: =RemBrandPrice(A1) the result is the exact same
information i have in column A1, it dosen't remove the Brand or Price at the
end. I tried this with all the 3900 products and it didn't do anything. What
am i doing wrong?
Click to expand...



Hmm -- no error messages.

I assume that you placed the VBA Code in a regular module, as I wrote, and the
function in cell B1; and then "filled down" so that the cell reference changed
with each line.

Is it possible that your data is in a different format than what you posted?

Do you have multiple lines in one cell? (I assumed one line per cell).
--ron
 
Top