Find character when the length varies

I have a worksheet where I am trying to get the first part of a string inside
a cell. See data:

ColumnA ColumnB (wanted result)
001-1- 001-1
001-10- 001-10
001-12- 001-12
001-85.98.8- 001-85.98.8

In ColA I have a dash that gives the first part (project) then the second
part can either be a task code (numeric digit) or a part number. In either
case I need the entire string upto the second dash. I was using a formula of
=Mid(a1,5) in ColB, but that of course doesn't do it. Any suggestions, and it
should be located within one column, its hard to use multiple columns to get
the answer. Then I would have used LEN() to count to second dash, etc.. It
all needs to be in one column.

=LEFT(A2,FIND("-",A2,FIND("-",A2)+1)-1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rookie_User" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet where I am trying to get the first part of a string
inside
> a cell. See data:
>
> ColumnA ColumnB (wanted result)
> 001-1- 001-1
> 001-10- 001-10
> 001-12- 001-12
> 001-85.98.8- 001-85.98.8
>
> In ColA I have a dash that gives the first part (project) then the second
> part can either be a task code (numeric digit) or a part number. In
either
> case I need the entire string upto the second dash. I was using a formula
of
> =Mid(a1,5) in ColB, but that of course doesn't do it. Any suggestions, and
it
> should be located within one column, its hard to use multiple columns to
get
> the answer. Then I would have used LEN() to count to second dash, etc..
It
> all needs to be in one column.

Assuming all of your data follows the examples you provided, I think this
will work for you:

=LEFT(A1,FIND("~",SUBSTITUTE(A1,"-","~",2),1)-1)

HTH,
Elkar


"Rookie_User" wrote:

> I have a worksheet where I am trying to get the first part of a string inside
> a cell. See data:
>
> ColumnA ColumnB (wanted result)
> 001-1- 001-1
> 001-10- 001-10
> 001-12- 001-12
> 001-85.98.8- 001-85.98.8
>
> In ColA I have a dash that gives the first part (project) then the second
> part can either be a task code (numeric digit) or a part number. In either
> case I need the entire string upto the second dash. I was using a formula of
> =Mid(a1,5) in ColB, but that of course doesn't do it. Any suggestions, and it
> should be located within one column, its hard to use multiple columns to get
> the answer. Then I would have used LEN() to count to second dash, etc.. It
> all needs to be in one column.

Your golden - thank you very much for all the help from both of you.

"Elkar" wrote:

> Assuming all of your data follows the examples you provided, I think this
> will work for you:
>
> =LEFT(A1,FIND("~",SUBSTITUTE(A1,"-","~",2),1)-1)
>
> HTH,
> Elkar
>
>
> "Rookie_User" wrote:
>
> > I have a worksheet where I am trying to get the first part of a string inside
> > a cell. See data:
> >
> > ColumnA ColumnB (wanted result)
> > 001-1- 001-1
> > 001-10- 001-10
> > 001-12- 001-12
> > 001-85.98.8- 001-85.98.8
> >
> > In ColA I have a dash that gives the first part (project) then the second
> > part can either be a task code (numeric digit) or a part number. In either
> > case I need the entire string upto the second dash. I was using a formula of
> > =Mid(a1,5) in ColB, but that of course doesn't do it. Any suggestions, and it
> > should be located within one column, its hard to use multiple columns to get
> > the answer. Then I would have used LEN() to count to second dash, etc.. It
> > all needs to be in one column.