Deleting certain string within the string
 

I have two colums. First column contains a product code and second contains
product code and some description. However I don't need product code in this
other column anymore. How am I able delete just this code string from other
column.

--
-Sam

Deleting certain string within the string
 

Hi Sam,

I have two colums. First column contains a product code and second
contains product code and some description. However I don't need
product code in this other column anymore. How am I able delete just
this code string from other column.

There are different ways to do this. Pls. post some examples. Has your
product code a fixed length? Is there a blank between product code and
description, if yes, is it the first blank of the text? Is your code
natural language or only digits ...

arno

Deleting certain string within the string
 

Hi,

One possibility:

Assume A1=1234 (Product Code)
B1=1234 ABCD.....(Product Code plus Description

Then in C1 put '=TRIM(REPLACE(TRIM(B1),1,LEN(TRIM(A1)),""))' (without single
quotes). this will leave ABCD in C1.

Copy the formula down column C as required.

HTH

"Sam" wrote:

I have two colums. First column contains a product code and second contains
product code and some description. However I don't need product code in this
other column anymore. How am I able delete just this code string from other
column.

--
-Sam

Deleting certain string within the string
 

That will work AS LONG AS the product code is not also found in the
description, e.g.

A1=12 (Product Code)
B1=12 Package of 120 nuts and bolts (Product Code plus Description)

will produce undesirable results ("Package of 0 nuts and bolts"). If the
product code is always in a specific location of the description string
(beginning, end, etc.), you may be able to use the Left(), Right() or Mid()
functions to strip the code.


"Toppers" wrote:

Hi,

One possibility:

Assume A1=1234 (Product Code)
B1=1234 ABCD.....(Product Code plus Description

Then in C1 put '=TRIM(REPLACE(TRIM(B1),1,LEN(TRIM(A1)),""))' (without single
quotes). this will leave ABCD in C1.

Copy the formula down column C as required.

HTH

"Sam" wrote:

I have two colums. First column contains a product code and second contains
product code and some description. However I don't need product code in this
other column anymore. How am I able delete just this code string from other
column.

--
-Sam

Deleting certain string within the string
 

Yes, here is some additional information.

Code is something like this "SKL190A" or "SKL190", so it can be six or seven
marks long. Other column contains something like this "SKL190A Input
device..."
There is a blank between code and description.

"arno" wrote:

Hi Sam,

I have two colums. First column contains a product code and second
contains product code and some description. However I don't need
product code in this other column anymore. How am I able delete just
this code string from other column.

There are different ways to do this. Pls. post some examples. Has your
product code a fixed length? Is there a blank between product code and
description, if yes, is it the first blank of the text? Is your code
natural language or only digits ...

arno

Deleting certain string within the string
 

Charlie,

SUBSTITUTE has a fourth parameter that can be used to select which instance
to replace.

HTH,
Bernie
MS Excel MVP


"Charlie" wrote in message
...
That will work AS LONG AS the product code is not also found in the
description, e.g.

A1=12 (Product Code)
B1=12 Package of 120 nuts and bolts (Product Code plus Description)

will produce undesirable results ("Package of 0 nuts and bolts"). If the
product code is always in a specific location of the description string
(beginning, end, etc.), you may be able to use the Left(), Right() or
Mid()
functions to strip the code.


"Toppers" wrote:

Hi,

One possibility:

Assume A1=1234 (Product Code)
B1=1234 ABCD.....(Product Code plus Description

Then in C1 put '=TRIM(REPLACE(TRIM(B1),1,LEN(TRIM(A1)),""))' (without
single
quotes). this will leave ABCD in C1.

Copy the formula down column C as required.

HTH

"Sam" wrote:

I have two colums. First column contains a product code and second
contains
product code and some description. However I don't need product code
in this
other column anymore. How am I able delete just this code string from
other
column.

--
-Sam

Deleting certain string within the string
 

Charlie,
Try you query .. you get" Package of 120 nuts and bolts" as it
only takes the length of A1 from the front of the combined Product +
description. It does NOT remove 12 from the 120. This REPLACE does work the
same as the VBA replace.






"Charlie" wrote:

That will work AS LONG AS the product code is not also found in the
description, e.g.

A1=12 (Product Code)
B1=12 Package of 120 nuts and bolts (Product Code plus Description)

will produce undesirable results ("Package of 0 nuts and bolts"). If the
product code is always in a specific location of the description string
(beginning, end, etc.), you may be able to use the Left(), Right() or Mid()
functions to strip the code.


"Toppers" wrote:

Hi,

One possibility:

Assume A1=1234 (Product Code)
B1=1234 ABCD.....(Product Code plus Description

Then in C1 put '=TRIM(REPLACE(TRIM(B1),1,LEN(TRIM(A1)),""))' (without single
quotes). this will leave ABCD in C1.

Copy the formula down column C as required.

HTH

"Sam" wrote:

I have two colums. First column contains a product code and second contains
product code and some description. However I don't need product code in this
other column anymore. How am I able delete just this code string from other
column.

--
-Sam

Deleting certain string within the string
 

... should have said does NOT work like VBA.

"Toppers" wrote:

Charlie,
Try you query .. you get" Package of 120 nuts and bolts" as it
only takes the length of A1 from the front of the combined Product +
description. It does NOT remove 12 from the 120. This REPLACE does work the
same as the VBA replace.






"Charlie" wrote:

That will work AS LONG AS the product code is not also found in the
description, e.g.

A1=12 (Product Code)
B1=12 Package of 120 nuts and bolts (Product Code plus Description)

will produce undesirable results ("Package of 0 nuts and bolts"). If the
product code is always in a specific location of the description string
(beginning, end, etc.), you may be able to use the Left(), Right() or Mid()
functions to strip the code.


"Toppers" wrote:

Hi,

One possibility:

Assume A1=1234 (Product Code)
B1=1234 ABCD.....(Product Code plus Description

Then in C1 put '=TRIM(REPLACE(TRIM(B1),1,LEN(TRIM(A1)),""))' (without single
quotes). this will leave ABCD in C1.

Copy the formula down column C as required.

HTH

"Sam" wrote:

I have two colums. First column contains a product code and second contains
product code and some description. However I don't need product code in this
other column anymore. How am I able delete just this code string from other
column.

--
-Sam

Deleting certain string within the string
 

Hi Sam,

so it can be six
or seven marks long.
....
There is a blank between code and description.

then your formula for a code+text in B1 is:
=RIGHT(B1,LEN(A1)-FIND(" ",B1))

This formula searches for the first blank in the text and takes
everything from there to the end of the string (you have to know how
long your string is (LEN), at which position the blank is (FIND) - then
you know how many characters you have to take from the right end of the
text (RIGHT)).

Do NOT use other functions (eg. trim) to manipulate the resulting
string unless it is absolutely neccessary! E. g. there could be a
reason for blanks to be where they are.

arno

Deleting certain string within the string
 

sorry:

correct formula
=RIGHT(B1,LEN(B1)-FIND(" ",B1))

Deleting certain string within the string
 

Hi Sam,
this is a formula for worksheets:
=RIGHT(B1,LEN(B1)-FIND(" ",B1))

in vba you can try worksheetfunction.find, at the moment I cannot
recall a vba equivalent.

and there's something wrong with "var", try this

Sub test()
frR = 1
Do While Cells(frR, 1) < ""
Var = Cells(frR, 1).Value
Cells(frR, 3) = Right(Var, Len(Var) - WorksheetFunction.Find(" ",
Var))
frR = frR + 1
Loop
End Sub


arno

Deleting certain string within the string
 

arno wrote:
in vba you can try worksheetfunction.find, at the moment I cannot
recall a vba equivalent.
have a look at instr-function in vba help.

arno