VBA: replace numbers in one column with text

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,366
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
For the sake of learning a new technique in VBA, the code below works fine to replace certain numbers with text, but can it be converted into an array construct where it uses Lbound and Ubound? Is there a better method?

Code:
[FONT=arial]Sub ReplaceNumbers()[/FONT]
[FONT=arial]    Dim c As Range[/FONT]
[FONT=arial]    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))[/FONT]
[FONT=arial]        Select Case c.Value[/FONT]
[FONT=arial]        Case 1[/FONT]
[FONT=arial]            c.Value = "Working Project"[/FONT]
[FONT=arial]        Case 2[/FONT]
[FONT=arial]            c.Value = "Delayed Project"[/FONT]
[FONT=arial]        Case 3[/FONT]
[FONT=arial]            c.Value = "Completed Project"[/FONT]
[FONT=arial]        End Select[/FONT]
[FONT=arial]    Next c[/FONT]
[FONT=arial]End Sub[/FONT]
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
This does not address your question. But is another way to do what you want.

Code:
Sub ReplaceNumbers()
    Dim c As Range
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Select Case c.Value
        Case c.Value
        c.Value = Choose(c.Value, "Working Project", "Delayed Project", "Completed Project")
        
        End Select
    Next c
End Sub
 
Upvote 0
@ FryGirl

For 3 values like that, I would probably just go with code that does 3 Find/Replace processes on the column.
If the data is very large and there are many different values to find/replace then an array could be used and would be considerably faster than cycling through the actual cells as your sample code does.

Examples of each approach:
Code:
Sub Find_Replace_v1()
  Dim Bits As Variant
  Dim i As Long
  
  Const FindRepl As String = "1|Working Project|2|Delayed Project|3|Completed Project"
  
  Bits = Split(FindRepl, "|")
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    For i = 0 To UBound(Bits) - 1 Step 2
      .Replace What:=Bits(i), Replacement:=Bits(i + 1), LookAt:=xlWhole
    Next i
  End With
  Application.ScreenUpdating = True
End Sub


Sub Find_Replace_v2()
  Dim a As Variant
  Dim i As Long
  Dim Repl As String
  
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      Select Case a(i, 1)
        Case 1: Repl = "Working Project"
        Case 2: Repl = "Delayed Project"
        Case 3: Repl = "Completed Project"
        Case Else: Repl = a(i, 1)
      End Select
      a(i, 1) = Repl
    Next i
    .Value = a
  End With
End Sub
 
Upvote 0
FryGirl,

Here is a macro code that does not do any looping thru the rows in column A that contain 1, 2, or, 3.

Please try the following macro on a copy of your workbook/worksheet.

Code:
Sub ReplaceNumbers_V2()
' hiker95, 02/25/2018, ME1045103
Dim Addr As String
Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""1"",@)),""Working Project"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""2"",@)),""Delayed Project"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""3"",@)),""Completed Project"",@)", "@", Addr))
Columns(1).AutoFit
End Sub
 
Last edited:
Upvote 0
Here is a macro code that does not do any looping thru the rows in column A that contain 1, 2, or, 3.
Though note that the description was "to replace certain numbers with text". To me that implies that the data could contain other numbers (or in fact any other values, including blank) as well as 1,2,3. If that is possible, then your code could produce considerably different results to the OP's code.
 
Upvote 0
Thank you all for the responses.

My overall data set will be around 500 cells, so not that large. Just for fun and to learn more about code and efficiency, I ran some tests on 100000 cells.

The results
ReplaceNumbers_V2 - hiker95
2.516 secs
Find_Replace_v1 - Peter
2.484 secs
Find_Replace_v2 - Peter
0.438 secs
ReplaceNumbers - MAIT
0.430 secs
ReplaceNumbers2 - FryGirl
0.422 secs

@Peter

an array could be used and would be considerably faster

I'm guessing v2 is the array version?
 
Last edited:
Upvote 0
.. I ran some tests on 100000 cells.

The results
ReplaceNumbers_V2 - hiker95
2.516 secs
Find_Replace_v1 - Peter
2.484 secs
Find_Replace_v2 - Peter
0.438 secs
ReplaceNumbers - MAIT
0.430 secs
ReplaceNumbers2 - FryGirl
0.422 secs
I am extremely surprised by some of those results but before questioning them I should ask what your sample data was like?
Was it 100,000 rows all of which contained just 1, 2 or 3? Or were there any other numbers, blank cells, text values in the range? Would/might your real data perhaps have any of these alternatives?

Also, is ReplaceNumbers2 - FryGirl the original code from post #1 just renamed?


I'm guessing v2 is the array version?
Correct.
 
Upvote 0
Hi Peter,

Was it 100,000 rows all of which contained just 1, 2 or 3?

Yes.

The real data will only consist of numbers, and it this time, there is only one number outside of the 1,2, or 3 which is not a concern.

Also, is ReplaceNumbers2 - FryGirl the original code from post # 1 just renamed?

Yes.

Since post # 2 had the same name, I renamed mine.
 
Last edited:
Upvote 0
The real data will only consist of numbers, and it this time, there is only one number outside of the 1,2, or 3 which is not a concern.
You say that there is only one number outside 1, 2 or 3 so it isn't a concern. I would suggest that it may be a concern, depending on which, if any, of the suggested codes you choose & what should happen with any such values - see blue comments below.

I also previously expressed surprise about your timing data so I did some tests too. For the timing tests I generated 100,000 rows randomly containing only 1, 2 or 3 and then duplicated that identical worksheet for each of the tests.
I have listed my times in red beside yours, and added a couple of notes about my point above. As different machines will run at different speeds, it is the relative speed of the various codes that would be relevant.

Just for fun and to learn more about code and efficiency, I ran some tests on 100000 cells.

The results
ReplaceNumbers_V2 - hiker95
2.516 secs <del>0.073</del> 0.730 (If any 'extra' numbers contain a 1, 2 or 3 digit - eg 14 - the cell will be replaced by one of the 3 text options)
Find_Replace_v1 - Peter
2.484 secs 1.375
Find_Replace_v2 - Peter
0.438 secs 0.164
ReplaceNumbers - MAIT
0.430 secs 5.547 (Cells containing any numbers other than 1,2,3 will be cleared)
ReplaceNumbers2 - FryGirl
0.422 secs 5.180
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top