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!)?

On 03/02/2011 10:42, Robert Cooper wrote:
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.

--
Dave Peterson

On Mar 2, 4:12*pm, Dave Peterson wrote:
Can you just import it with the dashes and then do an Edit|Replace to remove
those dashes (select the area first!)?

On 03/02/2011 10:42, Robert Cooper wrote:

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.

--
Dave Peterson

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


On Wed, 2 Mar 2011 14:18:03 -0800 (PST), Robert Cooper
wrote:

On Mar 2, 4:12*pm, Dave Peterson wrote:
Can you just import it with the dashes and then do an Edit|Replace to remove
those dashes (select the area first!)?

On 03/02/2011 10:42, Robert Cooper wrote:

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.

--
Dave Peterson

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.

On Mar 2, 11:16*pm, Gord Dibben wrote:
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

On Wed, 2 Mar 2011 14:18:03 -0800 (PST), Robert Cooper
wrote:



On Mar 2, 4:12 pm, Dave Peterson wrote:
Can you just import it with the dashes and then do an Edit|Replace to remove
those dashes (select the area first!)?

On 03/02/2011 10:42, Robert Cooper wrote:

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.

--
Dave Peterson

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.- Hide quoted text -

- Show quoted text -

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