Delete part of cell contents
 

Hi everyone,

I have a large excel sheet with a column containing data that has a
following format:

ABC - Item1
BCDE - Item20
EDFGS - Item345

And I need to separate the wording of the cell without using the Text
To Columns approach. Specifically, I need to delete everything before
the "-" , keeping only the "Item#" part. The Text To Columns approach
will create another column next to the original one, which is why I
don't want to use it because their is a very complex macro in the file
that uses the data in the order they are in. Adding column will alter
the ranges and could cause problems.

Next to this data column, there is an empty column that I can use. I
can use the Left,Righ, or Mid Approach to set the value of the cell
next to it, but as you can see, the number of character in the prefix
as well as that of the string of interest are not constant. And excell
does not understand the Wild Card "*"...

I don't know what to do. I really don't want to rewrite the macro to
take into account the extra column.

Thanks for your help.

V

Delete part of cell contents
 

Try this, seems to state that you wanted to keep the - so i left it

=RIGHT(A1,FIND("-",A1))

"nxqviet" wrote in message
ups.com...
Hi everyone,

I have a large excel sheet with a column containing data that has a
following format:

ABC - Item1
BCDE - Item20
EDFGS - Item345

And I need to separate the wording of the cell without using the Text
To Columns approach. Specifically, I need to delete everything before
the "-" , keeping only the "Item#" part. The Text To Columns approach
will create another column next to the original one, which is why I
don't want to use it because their is a very complex macro in the file
that uses the data in the order they are in. Adding column will alter
the ranges and could cause problems.

Next to this data column, there is an empty column that I can use. I
can use the Left,Righ, or Mid Approach to set the value of the cell
next to it, but as you can see, the number of character in the prefix
as well as that of the string of interest are not constant. And excell
does not understand the Wild Card "*"...

I don't know what to do. I really don't want to rewrite the macro to
take into account the extra column.

Thanks for your help.

V

Delete part of cell contents
 

This gets rid of the - and leaves the rest

=RIGHT(A1,FIND("-",A1,1)-1)

"nxqviet" wrote in message
ups.com...
Hi everyone,

I have a large excel sheet with a column containing data that has a
following format:

ABC - Item1
BCDE - Item20
EDFGS - Item345

And I need to separate the wording of the cell without using the Text
To Columns approach. Specifically, I need to delete everything before
the "-" , keeping only the "Item#" part. The Text To Columns approach
will create another column next to the original one, which is why I
don't want to use it because their is a very complex macro in the file
that uses the data in the order they are in. Adding column will alter
the ranges and could cause problems.

Next to this data column, there is an empty column that I can use. I
can use the Left,Righ, or Mid Approach to set the value of the cell
next to it, but as you can see, the number of character in the prefix
as well as that of the string of interest are not constant. And excell
does not understand the Wild Card "*"...

I don't know what to do. I really don't want to rewrite the macro to
take into account the extra column.

Thanks for your help.

V

Delete part of cell contents
 

John,

It worked perfectly...Thanks so much, I don't have to rewrite my macro
now..thanks.

Viet


John Bundy wrote:
This gets rid of the - and leaves the rest

=RIGHT(A1,FIND("-",A1,1)-1)

"nxqviet" wrote in message
ups.com...
Hi everyone,

I have a large excel sheet with a column containing data that has a
following format:

ABC - Item1
BCDE - Item20
EDFGS - Item345

And I need to separate the wording of the cell without using the Text
To Columns approach. Specifically, I need to delete everything before
the "-" , keeping only the "Item#" part. The Text To Columns approach
will create another column next to the original one, which is why I
don't want to use it because their is a very complex macro in the file
that uses the data in the order they are in. Adding column will alter
the ranges and could cause problems.

Next to this data column, there is an empty column that I can use. I
can use the Left,Righ, or Mid Approach to set the value of the cell
next to it, but as you can see, the number of character in the prefix
as well as that of the string of interest are not constant. And excell
does not understand the Wild Card "*"...

I don't know what to do. I really don't want to rewrite the macro to
take into account the extra column.

Thanks for your help.

V