Need macro to remove text

[jocote46]

I am very new using macros so please be understanding. I have several spreadsheets that have same column with same information.

Original text: Production-Valecia, Production-Pasadena, Production-San Diego etc. (total of 21 different locations) I want to create a macro that will delete the word "production" and just leave the name of the city.

Thank you in advance for your help.

[mdmackillop]

Welcome to VBAX

Change the column number to suit


Sub Macro1()
Dim sh As Worksheet
For Each sh In Sheets
sh.Columns(5).Replace What:="Production-", Replacement:=""
Next
End Sub

[jocote46]

Thanks for your reply, the code works great. However I do have another question. However I came accross something else, it looks like there are a couple of variations to the word "Production-Valencia" see below:

1. Production-Valencia
2. Production - Valencia
3. Production Connect - Valencia

So now I have 3 variables that I have to deal with, the end result is to get rid of the three variables and just get "Valencia"

Any suggestions?

[GTO]

Using Malcom's lead, but only if your latest sample data is inclusive. If not, I would suggest an example workbook with a goodlier amount of data.


Sub Macro11()
Dim sh As Worksheet
For Each sh In Sheets
sh.Columns(5).Replace What:="Production*-", Replacement:=""
sh.Columns(5).Replace What:=Chr(32), Replacement:=vbNullString
Next
End Sub
Mark

[jocote46]

I have attached a file with a small sample of the data.

Thanks again for your help.

[GTO]

Hi there,

Based on the sample data, it would seem that we could just use the hyphen. As I understood your first post, it appeared that you wished the values 'fixed in place', which this does.

In a Standard Module:


Option Explicit

Sub StripInPlace()
Dim rngLastRow As Range, rngDataToStrip As Range, rngCell As Range
'// Change to suit, Col C = 3, D = 4, etc
Const ColumnNumber As Long = 3
'// Same as above
Const StartingRow As Long = 2
'// Change sheetname to suit//
With ThisWorkbook.Worksheets("MySheet")
Set rngLastRow = RangeFound(Range(.Cells(StartingRow, ColumnNumber), .Cells(Rows.Count, ColumnNumber)))
'// A "safety" in case of an empty sheet.//
If rngLastRow Is Nothing Then Exit Sub
'// Note that although Range appears unqualified, it actually is, //
'// as the start and end of the range are both referenced to //
'// 'MySheet'. //
Set rngDataToStrip = Range(.Cells(StartingRow, ColumnNumber), rngLastRow)
For Each rngCell In rngDataToStrip
If Not InStr(1, rngCell.Value, "-") = 0 Then
'// Basically, Instr is used to get the starting point for Mid, //
'// the return of which is then Trim(med), and converted to //
'// Proper case. //
rngCell.Value = StrConv( Trim( Mid(rngCell.Value, InStr(1, rngCell.Value, "-") + 1) ), vbProperCase )
End If
Next
End With
End Sub

'// See vba help for the Find Method. The below is just using .Find with defaults //
'// to find the last row with data. //
Function RangeFound(SearchRange As Range, _
Optional FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range
If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If
Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function


I am 'formula challenged', but if you wanted to return the stripped values in another column, I think this would work.


=PROPER(TRIM(MID(C2,FIND("-",C2)+1,255)))

Hope that helps,

Mark

[GTO]

:omg2: Or... I hear tell of a magical thing called Text To Columns under Data on the menubar. Evidently this wonderous invention even includes the option of skipping a column, so you could just skip the first column and use the hyphen as the delimiter.

Sigh...

[jocote46]

Thank you all for your help, you guys are great!..all of your suggestions seem to be working and is going to save me tons of time with one click.

Thank so much again for solving my problem.