Solved: Search first word of cell.

[cjyogz]

I have sheets that on a certain cell, A2:C2, the first word is the Month, eg, April or March etc. I need a code to search this specific cell to determine what the first word is.

I then plan on savings this first word into a cell on a sheet called Data, on say, Cell E5 so that i can refer the word when i need to.

Can anyone help me?

[Killian]

So I guess one way is to search the text of the specified ranged for the first space and return the string up to that point. If there's no space then return the whole string.Function GetFirstWord(addr As String) As String
Dim rng As Range
Dim firstspace As Long

Set rng = ActiveSheet.Range(addr)
firstspace = InStr(1, rng.Text, " ")
If firstspace = 0 Then
GetFirstWord = rng.Text
Else
GetFirstWord = Left(rng.Text, firstspace - 1)
End If
End Function

'usage
Sub test()
MsgBox GetFirstWord("E5")
End Sub

[Shazam]

You would like to retrieve the first word in a row correct? If so, see if this helps you.



=INDEX(A2:C2,MATCH(TRUE,INDEX(ISTEXT(A2:C2),0),0))

Formula is an-arry

Must hold down:

Ctrl,Shift,Enter

[Cyberdude]

An alternative might be:
if cell "A1" contains "April Committments" and you want to extract "April" then
Sub Get1stWord()
Dim ary
ary = Split(Range("A1"), " ")
MsgBox ary(0)
End Sub which should display April.

P.S. I should have written "Dim ary As Variant".

[mdmackillop]

Too much typing!
To return for one cell only put this code in a standard module and enter =FW(A1) in your target cell

Function FW(Data As Range)
FW = Split(Data)
End Function

[Shazam]

I think this will help.

=LEFT(A1,FIND(" ",A1,1))

[mdmackillop]

To check a range of cells use

Function FWW(Data As Range)
Dim cel as range, tmp as String
For Each cel In Data
tmp = Trim(tmp & " " & cel)
Next
FWW = Split(tmp)
End Function

[cjyogz]

Hey thanks for the help people. Much appreciated.

Killian, tried your code, it received the first word, but i couldn't figure out a way to make it insert that data onto the sheet Data in Cell E5.

Thanks the for effort man.

Cyberdude, exactly what i wanted, made a few adjustments to make it insert data onto sheet Data Cell E5 and bobs my uncle. Thanks man.

Shazzam, sorry man, should of specified that i wanted it in VBA code, thanks for the tips though man.

mdmckillop, you were a great help last time, and i have no doubt that your code will work, i'm not as highly skilled in excel as you are, so i'm sticking with Cyberdudes code, because i can refer to that and actually understand what its doing incase i get an error later on when i change things around. Thanks for the effort again man.

Cheers all

[Cyberdude]

"bobs my uncle"??
(You're welcome.)

[cjyogz]

haha just a bit fo Aussie Slang!