changing text to general using

    I am trying to change the text format from a text cell to a general format cell i currently running a FOR NEXT loop.


    this is what i currently have but its not making any changes,


    Set objCell = objWksh.Cells(CurrentRow, 16)

    With objCell.Select

    objCell.Replace.NumberFormat = "General"

    objCell.Replace.Value = .Value

    End With


    any thoughts

    I have tried that and still will not convert the data.


    Set objCell = objWksh.Cells(CurrentRow, 16)

    With Selection

    .NumberFormat = "General"

    .Value = .Value

    End With


    also tried

    Set objCell = objWksh.Cells(CurrentRow, 16)

    With objCell

    .NumberFormat = "General"

    .Value = .Value

    End With


    i tried multiple ways but the numbers do not change from text format to general format


    here is an example data sheet i am using


    Assignment group Catalog Task Request Created User status




    		Last First
    		     Data convert to number








    		     63683














    		63683

    Consider:

    Code
    Sub Text2Num()
Dim cel As Range
    For Each cel In Selection
        cel.NumberFormat = "General"
        If IsNumeric(cel) Then cel = CDbl(cel)
    Next
End Sub


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    Rather than "General", try this


    Code
    objWksh.Cells(1, 1).CurrentRegion.Columns(16).NumberFormat = "#"

    Any numbers will converted to a true number (if you need decimal places change "#" to "#.00" , the number of zeros will adjust the number of decimal places shown), if the cell contains a text string (i.e. not a number) then it will remain as a text even when formatted as "#".


    Thus, the above code will change the formatting in all cells to Number Format in one go, there is no need for a For.....Next loop

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    this is my current code:




    Edited once, last by royUK ().

    BadBunny - I have added Code Tags. Please read the Forum Rules and follow them before posting again.


    This is the second warning. I will lock your post next time

    BadBunny, I tried your code and found that after changing the cell format, the line .Value = .Value did, indeed, convert the number-as-text to an actual number.


    KjBox, a loopless solution would be great and may exist, but I did not find that numbers-as-text got changed into actual numbers using your solution.


    Edit: Well, duh. Of course it can be done. Just replicate BadBunny's solution by adding another line:


    Code
    objWksh.Cells(1, 1).CurrentRegion.Columns(16).value = objWksh.Cells(1, 1).CurrentRegion.Columns(16).value


    (Can surely be cleaned up further using a With statement.)


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    A loop-less solution is possible, but because it has to be an object based code that has to make calls to the worksheet for each "Replacement". Also with object based code it is not possible to combine multiple "Replace" functions into a single line of code and have it case insensitive.


    An alternative is to use a single For....Next loop with an array based code.


    I am attaching 2 files with the same data, one uses the loop-less code and the other the array looping code.


    Personally I think the array based looping code is preferable. Not only is it likely to be slightly faster (depends on the number of rows of data), it is also more versatile for any future changes.


    Codes assigned to the buttons are:

    Loop-less:

    Array loop:


    Note that in both cases I have applied "General" format to the entire data range, that is to replace all Text formatting, which is a problem waiting to happen and should be avoided whenever possible.

    Thank you all for the help


    KjBox - i like this. all is working how i wanted to the only thing is that i need to keep the SSN column as a text format with the 4 last digits. for the reason that they are some that start with a zero or end with a zero.


    Thanks

    Which code did you decide to use?


    Which column is the SSN column?


    Can you give a few examples of the data that could be in that column.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Do you mean that the SSN can start off as more that a 4 digit number, and you need only the last 4 digits to display with all zeros that may be present in those last 4 digits?


    It would be best if you could give me some examples of what the SSN entries could be.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Good morning, the SSN column is only the last 4 digits of the SSN, so i need it to stay as a text format other wise if the last 4 of the SSN has a "0" in a general format it would be removed. example the number could be 0045 or 0450 or 0677 etc. in this cases it would remove all the zeros and keep the higher digits starting from 1 and above. again all this is on header15


    Assignment group Catalog Task Request Created User status Epic Access User Transferring Effective Date User ID Requested for Last name First name Middle Initial DOB SSN

    Try

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited once, last by KjBox ().

    Thank you every one i was able to work with what i was given i truly appreciate the time you guys took to answer my questions

    Siemper Fi


    Thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login