texto to column by formula

[doriannjeshi]

Hi,

trying to trim this to the second delimiter by using a formula

111-11-1
result
111-11

exp2
ddd-dd-1
result
ddd-dd

 

[sheetspread]

Combine Find() with Mid()

 

[doriannjeshi]

How to find the second dash though

 

[doriannjeshi]

I want the result of the cell to be what comes before the second dash, but the number of characters can vary, how to do that ?

 

[Special-K99]

In your all too similar examples this will work

=LEFT(A1,6)

UPDATE: Ah, not very good examples really "the number of characters can vary"

Use this:

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

 

[Marcelo Branco]

Try

=LEFT(A1,SEARCH("|",SUBSTITUTE(A1,"-","|",2))-1)

Hope this helps

M.

 

[doriannjeshi]

Marcelo thank you,

I used this , is it ok?

LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1)

 

[Marcelo Branco]

Marcelo thank you,

I used this , is it ok?

LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1)

This formula was suggested by Special-K99 in post #5, not by me.
Yes, it works.


M.

 

[sheetspread]

Marcelo thank you,

I used this , is it ok?

LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1)

Yes, but Marcelo's is the best one, you can choose which dash to parse at by changing a single number.

 

[doriannjeshi]

When I enter this formula in a macro it says "invalid character" refering to "|"

=LEFT(A1,SEARCH("|",SUBSTITUTE(A1,"-","|",2))-1)