CODEWITHSUNDEEP
excelvba
BlueSun3k1
BlueSun3k1

Reputation: 767

Excel: VBA to TRIM initial strings that vary in lengh

Good Day to all,

I have VBA script that trims a specific lengnt of characters and then copies and pastes the remaining characters on another sheet but I've recently come across an issue. The initial lenght has now increased and the script leaves behind characters that shouldn't. Here's the original part of the script.

If Left(Sheets("NAMES").Cells(a, 3), 6) = "Emplo:" Then
ID = Right(Left(Trim(Sheets("NAMES").Cells(a, 3)), 11), 4)
Employee_Name = Trim(Right(Trim(Sheets("NAMES").Cells(a, 3)), Len(Trim(Sheets("NAMES").Cells(a, 3))) - 11))
End If

Originally, the initial string would read like this: Emplo: 1234 Sample, Name But now the initial string varies and the numbers after "Emplo: " are either 3, 4 or 8 digits long and I can't get the script to trim the proper amount of initial characters to keep the name intact.

Is it possible to fix this?

Thank you in advance.

Upvotes: 0

Views: 94

Answers (2)

Jagadish Dabbiru
Jagadish Dabbiru

Reputation: 940

As per your question you need to use split function effectively to get the solution

Var = Split(Sheets("NAMES").Cells(a, 3),":")(1)
ID = Split(LTrim(Var)," ")(0)
Employee_Name = Right(Var,Len(Var) - Len(ID))

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

We can break it apart using the space character:

If Left(Sheets("NAMES").Cells(a, 3), 6) = "Emplo:" Then
    ary = Split(Sheets("NAMES").Cells(a, 3), " ")
    ID = ary(1)
    Employee_Name = ary(2) & " " & ary(3)
End If

Upvotes: 1

Related Questions