How to use extract strings & store in required columns using VB Macro

[Vijaychitra]

Hi,

I'm new to VB Macro.. Need help to solve

A​	B​
Raw Data	Name (Expected result)
John-143-Accounts	John
Amy-102-IT	Amy
Bill-158-Marketing	Bill
Jack-198-Sales	Jack
Henry-147-IT	Henry

When used trim function in excel : TRIM(LEFT(A2,(FIND("-",A2,1)-1)) the desired result is obtained.

However, When used in the below format, error is thrown
Range("B2:B").Formula = "=TRIM(LEFT(A2,(FIND("-",A2,1)-1))"

I would like to know how this TRIM can be used in VBA or any other alternatives

 

[Marc L]

Hi, within a VBA string each double quote must be doubled and the range address must be valid like the formula as well ‼​

​

Range("B2:B6").Formula = "=TRIM(LEFT(A2,(FIND(""-"",A2,1)-1)))"​

​

 

[vletm]

Vijaychitra
One sample with VBA ...

Sub Do_It()
    With ActiveSheet
        b = 2
        Do
            .Cells(b, "B") = Trim(Left(Cells(b, "A"), WorksheetFunction.Find("-", .Cells(b, "A"), 1) - 1))
            b = b + 1
        Loop Until .Cells(b, "A") = Empty
    End With
End Sub

 

[Marc L]

If only the result is necessary without any formula in cells of the active worksheet :​

Sub Demo1()
    With [A1].CurrentRegion.Rows
        Range("B2:B" & .Count).Value2 = Evaluate(Replace("IF({1},LEFT(A2:A#,SEARCH(""-"",A2:A#)-1))", "#", .Count))
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Vijaychitra]

Hi, within a VBA string each double quote must be doubled and the range address must be valid like the formula as well ‼​

​

Range("B2:B6").Formula = "=TRIM(LEFT(A2,(FIND(""-"",A2,1)-1)))"​

​

Thanks It worked... It worked..

 

[Marc L]

According to your initial post column A sample the TRIM worksheet function is useless …​

​