VBA code to remove any text after certain characters

VAndre

New Member
Joined
Dec 7, 2005
Messages
42
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
 
Hi

Another option:

Code:
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.
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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!
 
Upvote 0
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...
Code:
[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]
 
Upvote 0
Your question is not entirely clear, but I think you may be asking for this...
Code:
[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!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top