Replace specific characters

[JackDanIce]

Hi,

I have the following list:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 102px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">211</TD><TD style="FONT-SIZE: 8pt">ROG VX</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">212</TD><TD style="FONT-SIZE: 8pt">VOD LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">213</TD><TD style="FONT-SIZE: 8pt">FP FP</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">214</TD><TD style="FONT-SIZE: 8pt">STAN LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">215</TD><TD style="FONT-SIZE: 8pt">MSPE</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">216</TD><TD style="FONT-SIZE: 8pt">VGU1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">217</TD><TD style="FONT-SIZE: 8pt">BNP FP</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">218</TD><TD style="FONT-SIZE: 8pt">IMT LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">219</TD><TD style="FONT-SIZE: 8pt">ABBN VX</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">220</TD><TD style="FONT-SIZE: 8pt">NDA SS</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">221</TD><TD style="FONT-SIZE: 8pt">BG/ LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">222</TD><TD style="FONT-SIZE: 8pt">DAI GR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">223</TD><TD style="FONT-SIZE: 8pt">SAMAS FH</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">224</TD><TD style="FONT-SIZE: 8pt">RDSA LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">225</TD><TD style="FONT-SIZE: 8pt">RI FP</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">226</TD><TD style="FONT-SIZE: 8pt">SWMA SS</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">227</TD><TD style="FONT-SIZE: 8pt">STB NO</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">228</TD><TD style="FONT-SIZE: 8pt">SX7E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">229</TD><TD style="FONT-SIZE: 8pt">DG FP</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">230</TD><TD style="FONT-SIZE: 8pt">VPK NA</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">231</TD><TD style="FONT-SIZE: 8pt">BATS LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">232</TD><TD style="FONT-SIZE: 8pt">TNET BB</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">233</TD><TD style="FONT-SIZE: 8pt">SIK SW</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">234</TD><TD style="FONT-SIZE: 8pt">BAYN GY</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">235</TD><TD style="FONT-SIZE: 8pt">XTA LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">236</TD><TD style="FONT-SIZE: 8pt">XTA LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">237</TD><TD style="FONT-SIZE: 8pt">CPG LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">238</TD><TD style="FONT-SIZE: 8pt">SAF FP</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">239</TD><TD style="FONT-SIZE: 8pt">DUFN SW</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">240</TD><TD style="FONT-SIZE: 8pt">CLLN LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">241</TD><TD style="FONT-SIZE: 8pt">BG/ LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">242</TD><TD style="FONT-SIZE: 8pt">CPW LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">243</TD><TD style="FONT-SIZE: 8pt">CPW LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">244</TD><TD style="FONT-SIZE: 8pt">CLLN LN</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">245</TD><TD style="FONT-SIZE: 8pt">CPG LN</TD></TR></TBODY></TABLE>




And I'm trying to write some code that will replace anything that ends with SE with SW, SQ with SM and GY with GR, e.g. row 234, I want to read BAYN GR instead of BAYN GY

So far I have:

For i = i To j
    If Len(Range("E" & i)) > 4 Then
        Select Case Right(Range("E" & i), 2)
            Case "SE"
                Range("E" & i) = Left(Range("E" & i), x) & " " & "SW"

But I'm struggling to define the value of x because with above list as example, it can be either 2, 3 or 4 - i.e. it's the position of the " " (space) in the cell - 1

Can someone help with this part please?

Thanks,
Jack

 

[Rick Rothstein]

Assuming the letters you want to replace always follow a space character, give this macro a try...

Sub ReplaceLetters()
  Dim X As Long, Letters() As String, Replacements() As String
  Letters = Split("SE SQ GY")
  Replacements = Split("SW SM GR")
  For X = 0 To UBound(Letters)
    Columns("A").Replace " " & Letters(X), " " & Replacements(X), xlPart
  Next
End Sub

 

[taurean]

Alternatively to continue with your original code, use:

Range("E" & i).Value = WorksheetFunction.Substitute(Range("E" & i).Value, "SE", "SW")

 

[JackDanIce]

Hi Rick,

Thanks for the reply, there's enough there that I can adjust (need it to work in a specific range of cells, rather than the whole column) but like the use of arrays as that's something I want to learn more about and use.

Thank you,
Jack

 

[JackDanIce]

taurean - good suggestion too, I'll have a think about which one to use.

Thanks,
Jack

 

[Rick Rothstein]

...need it to work in a specific range of cells, rather than the whole column...

You know your data... as long as there are on other text in the column with the letter combo you are looking to replace (preceded by a space), other than the text you are really looking to replace, it does not matter if you process the entire column... the cells without the letter combination (preceded by a space) will simply have nothing happen to them.... Replace only replaces text it finds. However, you can modify my code by replacing the Columns("A") reference with an actual cell Range (contiguous or not) and it should still work. Alternately, you can select the cells manually and use Selection in my code in place of the Columns("A") reference. For example, if you were going to select the ranges manually, then my code would become this to handle it...

Sub ReplaceLetters()
  Dim X As Long, Letters() As String, Replacements() As String
  Letters = Split("SE SQ GY")
  Replacements = Split("SW SM GR")
  For X = 0 To UBound(Letters)
    Selection.Replace " " & Letters(X), " " & Replacements(X), xlPart
  Next
End Sub

VB code offers lots of flexibility, but we need to know what to "flex" in order to handle what you are actually trying to do... you cannot leave it to us to guess.

 

[JackDanIce]

Rick,

I am aware of the flexibility in the code, that's why my reply said:

there's enough there that I can adjust (I need it to work on a specific range rather than a column)

And hence why I only showed a selection of the sheet. My reply was in case you wondered what I meant by adjust.

I know how frustrating it is when people are vague in the questions they post on this board and then complain it doesn't work, my reply was simply to say thank you and that it gave me a good base to work from and adapt.

Again though, appreciate your efforts and consideration.

Enjoy rest of your weekend,
Jack

 

[JackDanIce]

Rick,

I am aware of the flexibility in the code, that's why my reply said:

there's enough there that I can adjust (I need it to work on a specific range rather than a column)

And hence why I only showed a selection of the sheet. My reply was in case you wondered what I meant by adjust. Am also aware it can work on the whole column and it will only replace if it finds something, however, since I only want it to operate on a selection within the column (that I am already defining elsewhere in my code I hadn't posted), for speed considerations, I only want it to operate on the area I can define. You could argue, with the power of modern PC's, it's probably nothing that I'm saving in terms of performance by restricting the code to a specified area, but I like to programme in a certain way and so it's as I do.

I know how frustrating it is when people are vague in the questions they post on this board and then complain it doesn't work, my reply was simply to say thank you and that it gave me a good base to work from and adapt.

Again though, appreciate your efforts and consideration.

Enjoy rest of your weekend,
Jack

 

[Rick Rothstein]

...my reply was simply to say thank you and that it gave me a good base to work from and adapt.

In that case... you are quite welcome, I'm glad I was able to be of some help.