PDA

View Full Version : Solved: Search first word of cell.

cjyogz
08-11-2006, 07:39 PM
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
08-12-2006, 10:08 AM
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

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
08-12-2006, 11:48 AM
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
08-12-2006, 12:33 PM
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
08-12-2006, 01:16 PM
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
08-12-2006, 01:50 PM
I think this will help.

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

mdmackillop
08-12-2006, 01:59 PM
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
08-12-2006, 07:01 PM
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
08-12-2006, 08:13 PM
"bobs my uncle"??
(You're welcome.)

cjyogz
08-12-2006, 08:48 PM
haha just a bit fo Aussie Slang!