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.

Have you thought about Replace Ctrl + G or Substitute function? — Scott Holtzman
– Scott Holtzman, Commented Aug 9, 2012 at 20:14
I use the following cell formula to find a specific set of test. =IF(Q3="", 0, VLOOKUP(Q3,I$2:J$7, 2, FALSE)) Q3 is the value I am validating, this would be the data in the A column. The I column contains the From value and the J column contains the To value. The problem with this formula is it looks for the exact value. I need to find the substring. Is a variation of this formula possible ? — Michael W
– Michael W, Commented Aug 9, 2012 at 21:05

Stepan1010 · Accepted Answer · 2012-08-09 21:30:35Z

12

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:

Your Situation I Think

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):

answered Aug 9, 2012 at 21:30

Stepan1010

3,1341 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael W Over a year ago

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

Mac Ank · Accepted Answer · 2013-03-15 16:00:06Z

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

Collectives™ on Stack Overflow

Excel Function to Replace Substring

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related