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.

Find character when the length varies
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

Find character when the length varies
 

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.

Find character when the length varies
 

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.