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

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

"Gmata" wrote in message
...
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

"Tyro" wrote:

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

"Gmata" wrote in message
...
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


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.
Does that make sense now?

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.

Gmata wrote:

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

--

Dave Peterson

On Tue, 1 Apr 2008 17:52:01 -0700, Gmata
wrote:

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

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

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?

On Tue, 1 Apr 2008 19:56:03 -0700, Gmata
wrote:

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?



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