Let's say i have a column with the following cells :

A1 : house
A2 : garden
A3 : room
A4 : garden
A5: bedroom
A6 : garden
A7 : bedroom

I need a macro which transforms the words into predefined numbers . So the
results in column B should be like this

B1 : 1
B2 : 2
B3 : 3
B4 : 2
B5 : 4
B6 : 2
B7 : 4

Most likely , in my case , the cells contain only one word . What about if a
cell contains the key word and other words as well ( words which are not key
words ) . Something like this :

A1 : My house is a cottage
A2 : What a beautiful garden
A3 : Go to your room
A4 : I sold my garden yesterday
A5: I need another bedroom
A6 : I like my garden very much
A7 : Go to your bedroom and wash the floor

I would like the result to be the same as in the first case .

A1 : house
A2 : garden
A3 : room
A4 : garden
A5: bedroom
A6 : garden
A7 : bedroom

Can this be done ?

This macro should do what you want. Just set the constants in the three
Const statements to the values you want. Note that the WorkList constant
must be a comma delimited list of words or phrases... do *not* put any
spaces around the commas to "neaten" things up.

Sub DecodeWords()
Dim X As Long, Z As Long, LastRow As Long, Words() As String
Const StartRow As Long = 1
Const DataColumn As String = "A"
Const WordList As String = "house,garden,room,bedroom"
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
Words = Split(LCase(WordList), ",")
For X = StartRow To LastRow
For Z = 0 To UBound(Words)
If " " & LCase(Cells(X, DataColumn).Value) & " " _
Like "*[!a-z]" & Words(Z) & "[!a-z]*" Then
Cells(X, DataColumn).Offset(0, 1).Value = Z + 1
Exit For
End If
Next
Next
End Sub

--
Rick (MVP - Excel)


"andrei" wrote in message
...
Let's say i have a column with the following cells :

A1 : house
A2 : garden
A3 : room
A4 : garden
A5: bedroom
A6 : garden
A7 : bedroom

I need a macro which transforms the words into predefined numbers . So the
results in column B should be like this

B1 : 1
B2 : 2
B3 : 3
B4 : 2
B5 : 4
B6 : 2
B7 : 4

Most likely , in my case , the cells contain only one word . What about if
a
cell contains the key word and other words as well ( words which are not
key
words ) . Something like this :

A1 : My house is a cottage
A2 : What a beautiful garden
A3 : Go to your room
A4 : I sold my garden yesterday
A5: I need another bedroom
A6 : I like my garden very much
A7 : Go to your bedroom and wash the floor

I would like the result to be the same as in the first case .

A1 : house
A2 : garden
A3 : room
A4 : garden
A5: bedroom
A6 : garden
A7 : bedroom

Can this be done ?

It works . Thank you very much

"Rick Rothstein" wrote:

This macro should do what you want. Just set the constants in the three
Const statements to the values you want. Note that the WorkList constant
must be a comma delimited list of words or phrases... do *not* put any
spaces around the commas to "neaten" things up.

Sub DecodeWords()
Dim X As Long, Z As Long, LastRow As Long, Words() As String
Const StartRow As Long = 1
Const DataColumn As String = "A"
Const WordList As String = "house,garden,room,bedroom"
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
Words = Split(LCase(WordList), ",")
For X = StartRow To LastRow
For Z = 0 To UBound(Words)
If " " & LCase(Cells(X, DataColumn).Value) & " " _
Like "*[!a-z]" & Words(Z) & "[!a-z]*" Then
Cells(X, DataColumn).Offset(0, 1).Value = Z + 1
Exit For
End If
Next
Next
End Sub

--
Rick (MVP - Excel)


"andrei" wrote in message
...
Let's say i have a column with the following cells :

A1 : house
A2 : garden
A3 : room
A4 : garden
A5: bedroom
A6 : garden
A7 : bedroom

I need a macro which transforms the words into predefined numbers . So the
results in column B should be like this

B1 : 1
B2 : 2
B3 : 3
B4 : 2
B5 : 4
B6 : 2
B7 : 4

Most likely , in my case , the cells contain only one word . What about if
a
cell contains the key word and other words as well ( words which are not
key
words ) . Something like this :

A1 : My house is a cottage
A2 : What a beautiful garden
A3 : Go to your room
A4 : I sold my garden yesterday
A5: I need another bedroom
A6 : I like my garden very much
A7 : Go to your bedroom and wash the floor

I would like the result to be the same as in the first case .

A1 : house
A2 : garden
A3 : room
A4 : garden
A5: bedroom
A6 : garden
A7 : bedroom

Can this be done ?