Extract characters from a Cell using a VBA Formula

[Kurt]

I have the following VBA code:

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

This is the cell it is looking at:

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

I want the formula to extract the first characters before the ~ regardless if it is 3 or 4 or however many characters.

 

[mikerickson]

A formula like
=LEFT(A1, FIND("~",A1&" ~")-1)

will do what you want. Note that the first literal is one tilde, the second is [space][tilde]

 

[Kurt]

A formula like
=LEFT(A1, FIND("~",A1&" ~")-1)

will do what you want. Note that the first literal is one tilde, the second is [space][tilde]

I check the code and I had a wrong character there which I suspected!

Thank you for the confirmation Mike!

 

[Dossfm0q]

another Option

Sub addFormulaArray()


Cells.Clear
    [A1] = "MECH~CDA-CUP-PF~1 - CUP0915.2XL - Copper Reducer (P)"
    
     Range("B1:K1").FormulaArray = "=IFERROR(MID($A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1))),FIND(""~"",$A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1))))-FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1)))),"""")"
    
    ' or see 2nd line
    Range("B2:D2").FormulaArray = "=LEFT(A1, FIND(""-"",A1&""-"")-1)"


    Range("B2").Replace What:="A1", Replacement:="IFERROR(MID($A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1))),FIND(""~"",$A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1))))-FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1)))),"""")", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Find(What:="A1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        
End Sub

 

[Kurt]

another Option

Sub addFormulaArray()


Cells.Clear
    [A1] = "MECH~CDA-CUP-PF~1 - CUP0915.2XL - Copper Reducer (P)"
    
     Range("B1:K1").FormulaArray = "=IFERROR(MID($A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1))),FIND(""~"",$A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1))))-FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B3)+1)))),"""")"
    
    ' or see 2nd line
    Range("B2:D2").FormulaArray = "=LEFT(A1, FIND(""-"",A1&""-"")-1)"


    Range("B2").Replace What:="A1", Replacement:="IFERROR(MID($A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1))),FIND(""~"",$A1,FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1))))-FIND(""|"",SUBSTITUTE(""~""&$A1,""~"",""|"",(COLUMN()-COLUMN($B1)+1)))),"""")", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Find(What:="A1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        
End Sub

Thank you for your efforts but I need to stay with the format I am using.

That will not work for my present needs. Too many lines of code also, but thank you for your efforts.