Extracting the first word from a line

  • Thread starter Thread starter Kashgarinn
  • Start date Start date
K

Kashgarinn

Greetings, and thank you for reading this.

I have a column of cells with text in it.

I want to extract the first word from that text from each row, so I ca
compare that first word with other data.

The first word can be 3 letters up to 15 letters. How do I do this?

I'm a real newbie when it comes to vbs scripting, so any hel
appreciated.

K
 
Hi!

Try this formula (if the name is in cell A1):

=LEFT(TRIM(A1);SEARCH(" ";TRIM(A1))-1)


Best regards

Stefan Hägglund
Microsoft
 
Hey, thanks alot, it worked for strings with more than one word.

I guess I'll use an IF statement when the string is only one word i
length.

K
 
a macro solution to put the first word in the next column

Sub firstword()
For Each c In Selection
x = InStr(c, " ")
If x > 0 Then
c.Offset(,1) = Left(c, x)
Else
c.Offset(, 1) = c
End If
Next c
End Sub
 
Hi Kashgarinn!

Yes, you can use an IF statement for this, like this formula:
=IF(ISERROR(LEFT(TRIM(A2),SEARCH("
",TRIM(A2))-1))=TRUE,A2,LEFT(TRIM(A2),SEARCH(" ",TRIM(A2))-1))

Best regards

Stefan Hägglund
Microsoft
 
Another way is to make sure your string to search through always has a space
character:

Using a modified version of Sefan's formula:

=LEFT(TRIM(A1)&" ",SEARCH(" ",TRIM(A1)&" ")-1)
 
Back
Top