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

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

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

"nxqviet" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

This gets rid of the - and leaves the rest

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

"nxqviet" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >