Subsitute more than one words in the columns

[weakinexcel]

I need to change words in first columns and replace it in the next columns. There are too many cells for me to change.
For example,
A1: Slight
A2: Moderate
A3: Slight
A4: High
A5: Calm
A6: Rough
A7: Very Rough
A8: Calm

I need to change it to
B1: 1111
B2: 2222
B3: 1111
B4: 3333
B5: 4444
B6: 5555
B7: 6666
B8: 4444

Is there any easy way like macro?

 

[PapaTompa]

Hi WeakinExcel.

Could you have a 'lookup table' on a separate sheet with the values for each word?

Similar to:

Calm	4444
Slight	1111
Moderate	2222
High	3333
Rough	5555
Very Rough	6666

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
</tbody>

Then in each B column cell have the formula, =vlookup($A1,'LookupTable'!$A$1:$B$8,2,FALSE)

The 'LookupTable'! name would need to be changed to the sheet the lookup table is located in.

Best.

 

[hiker95]

weakinexcel,

If I understand you correctly, here is a macro solution for you to consider that does not do any looping thru the cells in column A.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A
1	Slight
2	Moderate
3	Slight
4	High
5	Calm
6	Rough
7	Very Rough
8	Calm
9
Sheet1

After the macro:

Excel 2007
	A
1	1111
2	2222
3	1111
4	3333
5	4444
6	5555
7	6666
8	4444
9
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReplaceWordWithNumber()
' hiker95, 07/21/2015, ME869953
Dim Addr As String
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  Addr = "A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row
  .Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(@),""Slight"",""1111""),"""")", "@", Addr))
  .Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(@),""Moderate"",""2222""),"""")", "@", Addr))
  .Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(@),""High"",""3333""),"""")", "@", Addr))
  .Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(@),""Calm"",""4444""),"""")", "@", Addr))
  .Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(@),""Very Rough"",""6666""),"""")", "@", Addr))
  .Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(@),""Rough"",""5555""),"""")", "@", Addr))
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReplaceWordWithNumber macro.

 

[hiker95]

weakinexcel,

I am going to assume that the words are actually part of a string.

If that is so, then can we see examples of the words in strings?

 

[weakinexcel]

Hi Papa Tompa,
Thanks for the suggestion. I never thought of using vlookup before.

 

[weakinexcel]

Hi hiker95,
I wish to keep the columns of words as it is, just that the columns next to it will have the subsitution

 

[hiker95]

Hi hiker95,
I wish to keep the columns of words as it is, just that the columns next to it will have the subsitution

weakinexcel,

Thanks for the update.

I am just checking in early (before we go out an play a round of golf) to see if I have any updates to do.

I will be back later today with an update.

 

[hiker95]

Hi hiker95,
I wish to keep the columns of words as it is, just that the columns next to it will have the subsitution

weakinexcel,

Here is another macro solution for you to consider that uses two arrays in memory.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results:

Excel 2007
	A	B
1	Slight	1111
2	Moderate	2222
3	Slight	1111
4	High	3333
5	Calm	4444
6	Rough	5555
7	Very Rough	6666
8	Calm	4444
9
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub FindNumberForWord()
' hiker95, 07/22/2015, ME869953
Dim words, numbers
Dim c As Range, i As Long
Application.ScreenUpdating = False
words = Array("Slight", "Moderate", "High", "Calm", "Rough", "Very Rough")
numbers = Array("1111", "2222", "3333", "4444", "5555", "6666")
With Sheets("Sheet1")   '<-- you can change the sheet name here
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    For i = LBound(words) To UBound(words)
      If words(i) = c Then
        c.Offset(, 1) = CLng(numbers(i))
        Exit For
      End If
    Next i
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FindNumberForWord macro.

 

[weakinexcel]

Hi Hiker95,

Thank you so much. This is what i need.

 

[weakinexcel]

Hiker 95, i need to decode back from numbers to words after everything is done but at a different sheet is there any macro i can use it for?