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

 

[Rick Rothstein]

Hi

Another option:

Sub DeleteAfter()

Range("A1:A1000").Replace " *", "", LookAt:=xlPart
Range("A1:A1000").Replace "-*", "", LookAt:=xlPart
[COLOR=#FF0000][B]Range("A1:A1000").Replace "/*", "", LookAt:=xlPart[/B][/COLOR]
End Sub

You missed one.

 

[excelbeginer]

You missed one.

Thank you all for your help.

The code that Rick provided works well. The clean text is populated in column B as intended. The only issue is some text appearing in date format e.g. APR01-342 appears as 01-Apr in column B. I will try formatting the original data to text.

 

[pgc01]

You missed one.

Thanks. I'm not surprised. I was already 90% asleep!

 

[master_crazy]

Dear Mr. Risk,
Your programe work well, but incase remove any text before character what is code for? Right function working not right. Thanks you first!

 

[Rick Rothstein]

Dear Mr. Risk,
Your programe work well, but incase remove any text before character what is code for? Right function working not right.

Your question is not entirely clear, but I think you may be asking for this...

[table="width: 500"]
[tr]
	[td]Sub Trunc4()
  Range("B1:B1000") = Evaluate(Replace("IF(ROW(),MID(@,FIND("" "",SUBSTITUTE(SUBSTITUTE(" & _
                               "@,""-"","" ""),""/"","" "")&"" "")+1,LEN(@)))", "@", "A1:A1000"))
End Sub[/td]
[/tr]
[/table]

 

[master_crazy]

Your question is not entirely clear, but I think you may be asking for this...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Trunc4()
  Range("B1:B1000") = Evaluate(Replace("IF(ROW(),MID(@,FIND("" "",SUBSTITUTE(SUBSTITUTE(" & _
                               "@,""-"","" ""),""/"","" "")&"" "")+1,LEN(@)))", "@", "A1:A1000"))
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Dear Mr.Risk,
Thank you very much!
That right, I want remove text before certain special character, some case we must use Virtual helper column for sheets and I your programe for it.
You are Master!