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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,493
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 12, 2013
Messages
1

ADVERTISEMENT

Thanks from Colombia !
 

excelbeginer

New Member
Joined
Oct 30, 2014
Messages
2
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,184
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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...
Code:
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.
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,054
Perhaps

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

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

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,184
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Perhaps

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

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

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

Another option:

Code:
Sub DeleteAfter()

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

Forum statistics

Threads
1,136,990
Messages
5,678,979
Members
419,796
Latest member
doctorgresham

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
Top