Stop Converting CUSIP Numbers to Scientific Notation!

[brncao]

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,

 

[Joe4]

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:

=SUBSTITUTE(A1,"-1","")

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

 

[brncao]

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]

Here is some code.
Just change the column reference and starting row values at the top of the macro to meet your requirements.

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