Iferror right and find function

[Kurt]

I am now trying to recreate my Excel VBA formula on a worksheet to pick it apart.

I have the following text in cell A1

MECH~CDA-CUP-PF~1 - CUP0915.2XL - Copper Reducer (P)

I have the following in cell A3:

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

I am getting the error you need to type an apostrophe first.

I tried that and I still get an error.

When I try this formula in VBA it does start to work.

Can someone clue me in?

 

[njimack]

What are you hoping to return based on the value in A1?

 

[Kurt]

What are you hoping to return based on the value in A1?

I am wanting to return this part of the string:

MECH~CDA-CUP

 

[njimack]

If your data will always be in that format...
=LEFT(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))-1)

 

[Kurt]

Thanks that does indeed work.

How can I put that in the following format:

    AddFormula TopLeft.Offset(1, 3).Resize(RowCount, 1), "IFERROR(=LEFT(AA & Row & ", Find("#", Substitute(A1, "-", "#", 2)) - 1)

I am getting a Compile error: Expected: end of statement.

I am trying to reverse engineer the vba code that was handed to me.

 

[rlv01]

In your other thread which you started yesterday,

https://www.mrexcel.com/forum/excel-questions/1086073-extract-characters-cell-using-vba.html

which appears to deal with the same question you are asking here, you first said that you needed to extract:

MECH~CDA-CUP

Then you said that was a mistake and what you actually wanted to extract was

CDA-CUP-PF

So in this new thread, are you sure it is "MECH~CDA-CUP" that you want?

 

[Kurt]

In your other thread which you started yesterday,

https://www.mrexcel.com/forum/excel-questions/1086073-extract-characters-cell-using-vba.html

which appears to deal with the same question you are asking here, you first said that you needed to extract:

MECH~CDA-CUP

Then you said that was a mistake and what you actually wanted to extract was

CDA-CUP-PF

So in this new thread, are you sure it is "MECH~CDA-CUP" that you want?

Yes I am now trying to get your formula to work on this line of code:

    AddFormula TopLeft.Offset(1, 3).Resize(RowCount, 1), "=LEFT(AA" & Row & ",FIND(""#"",SUBSTITUTE(AA" & Row & "," - ",""#"",2))-1)"

Thank you for all of your effort so far!!

 

[rlv01]

Yes I am now trying to get your formula to work...

I think you mean njimack's formula, not mine.

 

[Kurt]

I think you mean njimack's formula, not mine.

Yes you are correct