Need macro that transforms key words into predefined numbers

[andrei]

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 ?

 

[Rick Rothstein]

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

 

[andrei]

It works . Thank you very much

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)

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 ?