Yet another Replace question...

[jacobRadio]

First off, this is my first question ever posted to any Excel forum and I have been using Excel and VBA for several years. So I would like to point out that I have found every answer to every question (whether I liked the answer or not) without posting, so please don't be rude if I missed the thread where this was covered, because as it may be quite possible I missed it, I DID look. Plus, I'm very sensitve, so be nice.

I have a list in about 800 cells, which have a changing length and changing string.

(ex.)

AAABBBCCC_TST##.StringNameEtc
DDEEFF_TST##.OtherStringName

What I would like to do is be able to replace the first characters (no matter what the length) up until the "_". This would allow me to replace:

AAABBBCCC_TST##.StringNameEtc
and
DDEEFF_TST##.OtherStringName

With the same variable like:

AhhMuchBetter_TST##.StringNameEtc
AhhMuchBetter_TST##.OtherStringName

Thank you for your replies

 

[jmthompson]

will all 800 cells have the same prefix before "_"?

 

[jacobRadio]

will all 800 cells have the same prefix before "_"?

Yes.

The list will go from something like this:

AAABBBCCC_TST##.StringTextOne
AAABBBCCC_TST##.StringTextTwo
AAABBBCCC_TST##.StringTextThree
AAABBBCCC_TST##.StringTextFour
AAABBBCCC_TST##.StringTextFive

To:

OTHERPREFIX_TST##.StringOne
OTHERPREFIX_TST##.StringTwo
OTHERPREFIX_TST##.StringThree
OTHERPREFIX_TST##.StringFour
OTHERPREFIX_TST##.StringFive

(and so on)

 

[jmthompson]

Try (assuming your list in in column A starting in Row 1)

Sub MyPrefix()
Dim c As Range
Dim lastRow As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("A1:A" & lastRow)
    If c <> "" Then c.Value = "OTHERPREFIX" & Right(c, (Len(c) - WorksheetFunction.Find("_", c)) + 1)
Next c

End Sub

 

[jacobRadio]

Try (assuming your list in in column A starting in Row 1)

Sub MyPrefix()
Dim c As Range
Dim lastRow As Long
 
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("A1:A" & lastRow)
    If c <> "" Then c.Value = "OTHERPREFIX" & Right(c, (Len(c) - WorksheetFunction.Find("_", c)) + 1)
Next c
 
End Sub

Thank you!! That did exactly the what I needed. I didn't know about:

-WorksheetFunction.Find("_"), c)) + 1)