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]

For 3 letters simply make this change
AddFormula TopLeft.Offset(1, 2).Resize(RowCount, 1), "=IFERROR(LEFT(AA" & Row & ",FIND(""-"",AA" & Row & ")+3),"""")"

 

[Kurt]

Fluff in this example I need the CDA-CUP-PF to be displayed.

The MECH has already been taken care of. Again thanks for your efforts and everyone looking.

 

[rlv01]

A VBA UDF approach

For "CDA"

=extractword(A1,2,"~- ")​

For "MECH~CDA-CUP"

=SUBSTITUTE(extractword(SUBSTITUTE(A1,"-","$",1),1,"-"),"$","-",1)​

'Returns the designated word from a string, where WordDelimiters is a character string containing word delimiters, e.g. WordDelimiters = " ,[];"
Function ExtractWord(ByVal AnyString As String, ByVal WordNo As Long, ByVal WordDelimiters As String) As String
    Dim SA, I As Long, WCnt As Long

    If Len(WordDelimiters) > 1 Then
        For I = 1 To Len(WordDelimiters)
            AnyString = VBA.Replace(AnyString, Mid$(WordDelimiters, I, 1), Left(WordDelimiters, 1))
        Next I
    End If
    
    SA = Split(AnyString, Left(WordDelimiters, 1))
    WCnt = 0
    For I = 0 To UBound(SA)
        If SA(I) <> "" Then
            WCnt = WCnt + 1
            If WCnt = WordNo Then
                ExtractWord = SA(I)
                Exit For
            End If
        End If
    Next I
End Function

 

[Fluff]

Fluff in this example I need the CDA-CUP-PF to be displayed.

But you said

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

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

 

[Kurt]

I apologize for the confusion Fluff.

I need this type of code to display CDA-CUP-PF

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

However we need it to change using MID or RIGHT or ?? is quit OK.

 

[rlv01]

Fluff in this example I need the CDA-CUP-PF to be displaye.

For "CDA-CUP-PF"

=extractword(A1,2,"~")​

 

[Kurt]

For "CDA-CUP-PF"

=extractword(A1,2,"~")​

So would it be something like this:

AddFormula TopLeft.Offset(1, 2).Resize(RowCount, 1), "=IFERROR(=extractword(A1,2,""~"")

or something else based on this example of code

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

 

[rlv01]

So would it be something like this:

AddFormula TopLeft.Offset(1, 2).Resize(RowCount, 1), "=IFERROR(=extractword(A1,2,""~"")

I don't know what AddFormula is. I assume it is some sort of subroutine you have written to add formulas to a range? If it worked for your other formulas then it is reasonable to think it will work for this one; but I have no way to test or verify.

 

[Kurt]

I don't know what AddFormula is. I assume it is some sort of subroutine you have written to add formulas to a range? If it worked for your other formulas then it is reasonable to think it will work for this one; but I have no way to test or verify.

The word will change so I don't think I can use that syntax.

I need to follow this format:

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

To extract text something like this: "CDA-CUP-PF"

How do I get the above code to see the two hyphens to extract the text string?

 

[rlv01]

That is the value of using delimiters to extract text, it does not matter if the word changes, it only matters if the delimiters change.

If this is an example of your data

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

Then it suggests that what you are trying to do is extract data from a parts list or bill of materials. Such data tends to be organized. That is, the same information is usually in the same place. So far you have been asking for help without explaining the broader picture. That means that the solution to your first question (how to get "CDA") did not really help when the requirement became how to get "CDA-CUP-PF" instead.

You will get more effective help if you take some time and explain the data organization of your parts list and what pieces of information you are trying to extract. There might be a different approach that will more efficiently provide the data you want.

http://xyproblem.info/