Removing Dashes in Excel

  • Thread starter Thread starter Robert Cooper
  • Start date Start date
R

Robert Cooper

In Excel 2007 I am regularly importing information about bonds. The
import includes dashes in the CUSIP number. Is there a way to import
into Excel 2007 eliminating the dashes? I will the copy and paste the
information into Bloomberg and Bloomberg does not recognize the
dashes. I know how to eliminate the dashes following the import. I
am trying to eliminate that step as we will be doing this daily.
 
Can you just import it with the dashes and then do an Edit|Replace to remove
those dashes (select the area first!)?
 
Can you just import it with the dashes and then do an Edit|Replace to remove
those dashes (select the area first!)?
Click to expand...

It looks like that is going to be the answer. I was hoping to
eliminate a step because this is going to be a daily and sometimes
several times a day process. Thanks.
 
Try the macro recorder to import the data then remove the dashes.

Assign the macro to a button.

You can probably also add the copy and paste to Bloomberg steps in the same
macro.


Gord Dibben MS Excel MVP
 
Try the macro recorder to import the data then remove the dashes.

Assign the macro to a button.

You can probably also add the copy and paste to Bloomberg steps in the same
macro.

Gord Dibben     MS Excel MVP




- Show quoted text -
Click to expand...

You could use a statement like one of the following:

Columns("A").Replace What:=" - ", Replacement:=" ",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

Columns("A").Replace What:="-", Replacement:=" ", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

Columns("A").Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

depending on what replacement results you want.

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
Back
Top