Excel Function to Replace Substring

Question

I need to replace the values in cells that may contain certain values. Lets say I have the following values listed in the A column.

Trucking Inc.
New Truck Inc
ABV Trucking Inc, LLC

I want to be able to replace the following with a corresponding value. The following is a list contains in 2 columns. 1 Column is the From and the other is the To field.

      From      To
      " Inc."   ""
      " Inc"    ""
      " Inc, "  ""
      " LLC"    ""

The result should be:

Trucking
New Truck
ABV Trucking

Hope I am making sense here.

Solution 1

I'm making the same assumptions as Scott Holtzman - you probably want to use the SUBSITUTE function.

Example:

=SUBSTITUTE(A2,B2,C2)

This is the situation I am assuming for you:

Just as an asside(lol pun): You should learn how to take screen shots and then edit them with MS paint - that will get alot more questions answered correctly for you (just for future reference):

Solution 2

I you are trying to do this in a macro then you can also try VBA Replace statement. It is much better than substitute function.

You can use following macro to help your cause:

Sub ChangeTerms()
Dim rows As Integer
Dim cellvalue As String
Dim Newcellvalue As String
rows = ActiveSheet.UsedRange.rows.Count
For i = 1 To rows
cellvalue = ActiveSheet.Cells(i, 1).Value
Newcellvalue = Replace(cellvalue, " Inc.", "")
Newcellvalue = Replace(Newcellvalue, " Inc,", "")
Newcellvalue = Replace(Newcellvalue, " Inc", "")
Newcellvalue = Replace(Newcellvalue, " LLC", "")
ActiveSheet.Cells(i, 1).Value = Newcellvalue
Next i
End Sub

The heart of this macro is Replace function. If you need to know more about this function then please go through following resources:

http://www.exceltrick.com/formulas_macros/vba-replace-function

http://msdn.microsoft.com/en-us/library/bt3szac5(v=vs.80).aspx

Comments

[Scott Holtzman]

Have you thought about Replace Ctrl + G or Substitute function?

[Michael W]

For my needs this will definitely have to do. I just wish the From and To could be ranges like in the VLookup.