VBA code to remove any text after certain characters

[VAndre]

I'm writing a little macro that needs to copy a string from one place to another. In the process, I need it to truncate the name after any characters like " " or "-".

Not all entries will have either character in them... some will but not all

I started by trying to use the Application.worksheet.FIND, but that breaks when one of the characters isn't present.

Any help would be appreciated! I'm trying to simplify the question, so please assume that I really do need to do this in VBA and can't just use Edit/Find&Replace or a simple function solution.

Thanks!

Andre

 

[Derek]

Hi there

THIS HAS BEEN EDITED:

You might like to play around with something like this:

Sub Trunc()
Dim str1 As String
Dim str2 As String
str1 = Range("A1").Value
x = 1
Do Until str2 = " " Or str2 = "-"
If x = Len(str1) + 2 Then GoTo OUT
str2 = Mid(str1, x, 1)
x = x + 1
Loop
OUT:
Range("B1").Value = Left(str1, x - 2)
End Sub

This copes with truncating at the first space or dash, but you could add other instances to the Do until......line. This example truncs A1 and puts it in B1.

IMPORTANT:
This code was edited to stop the macro if none of the conditions were found. If you tried the original macro on text in A1 that did not contain a space or a dash, you should use Control+Break to stop the code continuing to run.

regards
Derek

 

[VAndre]

thank god you aussies are up!

 

[Peter_SSs]

For what it's worth, here's another suggestion:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Trunc2()
    <SPAN style="color:#00007F">Dim</SPAN> str1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    str1 = Range("A1").Value & " "
    str1 = Left(str1, InStr(str1, " ") - 1) & "-"
    str1 = Left(str1, InStr(str1, "-") - 1)
    Range("B1").Value = str1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[JhormanHkr]

Thanks from Colombia !

 

[excelbeginer]

For what it's worth, here's another suggestion:

Sub Trunc2()
****Dim str1 As String
****str1 = Range("A1").Value & " "
****str1 = Left(str1, InStr(str1, " ") - 1) & "-"
****str1 = Left(str1, InStr(str1, "-") - 1)
****Range("B1").Value = str1
End Sub

Hi,

I am new to Excel and intested to know how the above code can be modified to remove any text/characters after a (" " , "-"," / ") for an entire column A:A or within a range A1:A1000, as opposed to a single cell.

I am using Excel 2010.

Thanks in advance.

 

[Rick Rothstein]

Hi,

I am new to Excel and intested to know how the above code can be modified to remove any text/characters after a (" " , "-"," / ") for an entire column A:A or within a range A1:A1000, as opposed to a single cell.

I am using Excel 2010.

Here is one way...

Sub Trunc3()
  Range("[COLOR=#FF0000][B]B1:B1000[/B][/COLOR]") = Evaluate(Replace("IF(ROW(),LEFT(@,FIND("" "",SUBSTITUTE(SUBSTITUTE(" & _
                               "@,""-"","" ""),""/"","" "")&"" "")-1))", "@", "[COLOR=#008000][B]A1:A1000[/B][/COLOR]"))
End Sub

Note: The red text is the output range (it can be the same as the input if you want to overwrite the original data) and the green text is the data range itself.

 

[mikerickson]

Perhaps

Range("B1").Value = Split(Replace(Range("A1").Value, "-"," "), " ")(0)

If there are more characters, you could expand to something like

Range("B1").Value = Split(Replace(Replace(Replace(Range("A1").Value, "-"," "), ":" " "), ";" ," "), " ")(0)

 

[Rick Rothstein]

Perhaps

Range("B1").Value = Split(Replace(Range("A1").Value, "-"," "), " ")(0)

If there are more characters, you could expand to something like

Range("B1").Value = Split(Replace(Replace(Replace(Range("A1").Value, "-"," "), ":" " "), ";" ," "), " ")(0)

I think you missed this sentence from the last questioner's posting in Message #6 (the original posting was in 2006)...

"I am new to Excel and intested to know how the above code can be modified to remove any text/characters after a (" " , "-"," / ") for an entire column A:A or within a range A1:A1000, as opposed to a single cell."

 

[pgc01]

Hi

Another option:

Sub DeleteAfter()

Range("A1:A1000").Replace " *", "", LookAt:=xlPart
Range("A1:A1000").Replace "-*", "", LookAt:=xlPart
End Sub