If you come across a situation in which you require only first word in each line of a cell rather than all the character, then you must read this article. The following tutorial will you help you in retrieving only first word through VBA code in excel.
In this article, we will focus on how to keep first word only in each line of a cell separated by semicolon via macro codes.
Question: The data I am working in has sometimes multiple lines in each of the cell & I want to extract the first word only.
You can find original question here
Following is a snapshot of input text (Before Sheet)
Following is a snapshot of input text (After Sheet) in column A & requires output in column B; refer below snapshot:
To get the code; we need to follow the below steps to launch VB editor
Function FirstWordOnly(rng As Range) Dim Arr() As Variant Dim Count As Integer Dim i As Integer Count = Len(rng) - Len(Replace(rng, Chr(10), "")) If Count > 0 Then ReDim Arr(0 To Count) For i = 0 To Count If i = 0 Then Arr(i) = Left(rng, InStr(1, rng, " ") - 1) ElseIf j = 0 Then j = InStr(1, rng, Chr(10)) Arr(i) = Mid(rng, j + 1, InStr(j, rng, " ") - j) Else j = InStr(j + 1, rng, Chr(10)) Arr(i) = Mid(rng, j + 1, InStr(j, rng, " ") - j) End If Next FirstWordOnly = Join(Arr, "; ") Else If InStr(1, rng, " ") > 0 Then FirstWordOnly = Left(rng, InStr(1, rng, " ")) Else FirstWordOnly = rng End If End If End Function
Now, the VBA code is ready for use; we will use the newly created User Defined Function i.e. “FirstWordOnly” in Before Sheet.
In the above code, we have used COUNT, LEN, REPLACE, IF, INSTR, MID, CHR, LEFT, JOIN VBA functions.
Conclusion: Using UDF we can remove everything in each line of cell except first word through VBA. This function will work in all the versions from old to new i.e. Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.