Stop Converting CUSIP Numbers to Scientific Notation!

brncao

Board Regular
Joined
Apr 28, 2015
Messages
139
I've tried formatting the CUSIP column as text prior to importing the data. It works. However, when I try to do "Find and Replace" on CUSIPs that have "-1" appended to it and replace with a blank string to remove it, Excel converted the Text format to Scientific. Why is Excel messing with the format? How do I get it to stay put?

Thanks,
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,532
Office Version
  1. 365
Platform
  1. Windows
I've tried formatting the CUSIP column as text prior to importing the data. It works. However, when I try to do "Find and Replace" on CUSIPs that have "-1" appended to it and replace with a blank string to remove it, Excel converted the Text format to Scientific. Why is Excel messing with the format? How do I get it to stay put?
Yes, that is a known issue/weakness with the "Find/Replace" functionality. It will convert text that looks like numbers to numbers.
Best to handle it with a formula or VBA.

A formula would look like this:
Excel Formula:
=SUBSTITUTE(A1,"-1","")

If you would like a VBA solution, please provide specific, i.e. what range these values are found in.
 

brncao

Board Regular
Joined
Apr 28, 2015
Messages
139
VBA would be better.

VBA should work on any tables regardless of table name, and there is only 1 table object in a sheet. Table header name is "CUSIP/Ticker"
VBA should replace "-?" with "". The "?" is a wildcard character; i.e. 313384E21-1, 313384E21-2, 313384E21-A should all read 313384E21.

I hope that's clear.

Thank you,
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,532
Office Version
  1. 365
Platform
  1. Windows
Here is some code.
Just change the column reference and starting row values at the top of the macro to meet your requirements.
VBA Code:
Sub MyReplaceMacro()

    Dim cl As String
    Dim rw As Long
    Dim lr As Long
    Dim r As Long
    Dim x As Long
    
    Application.ScreenUpdating = False
    
'***SET THSEE VALUES BELOW***
'   Indicate which column to run this against
    cl = "A"
'   Indicate first row data starts on
    rw = 2
'****************************
    
'   Dynamically Find last row in column with data
    lr = Cells(Rows.Count, cl).End(xlUp).Row
    
'   Loop through all rows and make updates
    For r = rw To lr
'       Find last dash in entry
        x = InStrRev(Cells(r, cl), "-")
'       Make replacement if x>0
        If x > 0 Then
            Cells(r, cl).NumberFormat = "@"
            Cells(r, cl) = Replace(Cells(r, cl), "-", "")
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub
 

Forum statistics

Threads
1,144,611
Messages
5,725,302
Members
422,608
Latest member
bswg5882

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