Extract characters from a cell using VBA

[Kurt]

I am trying to extract another set of characters from this same line:

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

AddFormula TopLeft.Offset(1, 2).Resize(RowCount, 1), "=IFERROR(MID(AA" & Row & ",FIND(""`"",AA" & Row & ")+1,FIND(""~"",AA" & Row & ")-1-FIND(""`"",AA" & Row & ")),"""")"

In this piece of code I need to extract the CDA. I have tried changing the character to match the - but no luck so far.

Any ideas?

 

[Fluff]

The formula should be like
=MID(A1, FIND("~",A1)+1,FIND("-",A1)-FIND("~",A1)-1)

 

[Kurt]

When I try to add it like this:

    AddFormula TopLeft.Offset(1, 2).Resize(RowCount, 1), "=IFERROR(MID(A1,FIND("~",AA)+1,FIND("-",AA)-FIND("~",AA)-1)"

I get the error invalid character on the ~

 

[Kurt]

I added another set of double quotes but the formula is still not working.

 

[Fluff]

You'll need to add the Rows in like your original formula

 

[Kurt]

Yes that is correct. I am still getting an error on this code.

The code uses AA because I am doing some other manipulation with the rest of the code in this module, just FYII.

    AddFormula TopLeft.Offset(1, 2).Resize(RowCount, 1), "=IFERROR(MID(AA" & Row & ",FIND(""-"",AA" & Row & ")+1,FIND(""~"",AA" & Row & ")-1-FIND(""-"",AA" & Row & ")),"""")"

 

[Kurt]

I just got it to work using this line of code:

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

Thanks for your efforts Fluff!!

 

[Kurt]

One more item I need to add to the formula.

I need to add the third set of info in this data:

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

I need this code to display the MECH~CDA-CUP part of that string.

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

 

[Fluff]

will it always be three letters?

 

[Kurt]

will it always be three letters?

As far as I can tell yes.

Can you program that way for me and if there were not always three letters for learning purposes?