Find character when the length varies

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to sort part numbers, sometimes ignoring leading letters 8
Insert row(s) with vba 3
Some rules about Vlookup REPOSTED 6
To find rate of each item from item.xls and to copy price.xls 7
concatenating from a different cell within the same row 6
return specific character read from right to left 2
Why do the preceding zeros disappear when I remove dashes? 3
Cvt number to character 3

Back
Top