changing contents of column when Formatting

  • I have a list of about 300 dates in column b that are formated as january 2006 etc. I need to change them to jan-06 after i highlight them and format them the way i need them they still say january 2006 and i have to go through one by one changing them to jan-06 is there any easier way to do this?

  • Re: changing contents of column when Formatting


    Try select the column selct edit>clear>formats. This will set you to text numbers and then format the column as needed. Good luck

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Changing contents of column while Formating


    I have a list of about 300 dates in column b that are formated as january 2006 etc. I need to change them to jan-06 after i highlight them and format them the way i need them they still say january 2006 and i have to go through one by one changing them to jan-06 is there any easier way to do this?

  • Re: Changing contents of column while Formating


    If you're doing it manually (not via VBA), then you can highlight all of the cells you need to change, right-click and select format cells. Any changes you make here will apply to all cells. Excel should recognize "January 2006" as a date and formatting it properly should show "Jan-06"; you don't need to explicitly change the value of the cell. Hope this helps.

  • Re: Changing contents of column while Formating


    All of column b is formatted as fallows January 2006, febuary 2006 etc. I highlight all of column b and change the format . in the format menu i choose mar-01 but after i do this and it goes back to my page nothing is changed and i have to go in one by one and type jan 2 0 0 6 then when i move to the next cell it changes to jan-06. Im not sure if it matters but my list is being imported from another program(memsys) as a csv file

  • Re: Changing contents of column while Formating


    I believe the problem is that the values, at import time, were not recognized as a date but as normal text. Try right-clicking on the tab of your worksheet at the bottom (normally named Sheet1, Sheet2, etc) and click on "view code". Microsoft Visual Basic should pop up, and you should see a white page if you don't have any macros in your workbook. Paste the following code in the white space that you see:


    Code
    Public Sub ConvertToDates()
    Dim tempRange As Range
    Set tempRange = Columns("B:B")
    For Each cell In tempRange
        If IsDate(cell) Then
            cell = CDate(cell)
        End If
    Next cell
    End Sub


    Press the play button with your cursor somewhere in the code. Then return to your Excel spreadsheet and you should find the date formatted properly. The code looks through all the cells in column b, checks if the value looks like a date, and if it does, it will convert it into a date. You'll have to do whatever formatting you still need to do in Excel though. Feel free to delete the code once you're done with it.


    Hope this helps.

  • Re: changing contents of column when Formatting


    I believe the problem is that the values, at import time, were not recognized as a date but as normal text. Try right-clicking on the tab of your worksheet at the bottom (normally named Sheet1, Sheet2, etc) and click on "view code". Microsoft Visual Basic should pop up, and you should see a white page if you don't have any macros in your workbook. Paste the following code in the white space that you see:


    Code
    Public Sub ConvertToDates() 
        Dim tempRange As Range 
        Set tempRange = Columns("B:B") 
        For Each cell In tempRange 
            If IsDate(cell) Then 
                cell = CDate(cell) 
            End If 
        Next cell 
    End Sub


    Press the play button with your cursor somewhere in the code. Then return to your Excel spreadsheet and you should find the date formatted properly. The code looks through all the cells in column b, checks if the value looks like a date, and if it does, it will convert it into a date. You'll have to do whatever formatting you still need to do in Excel though. Feel free to delete the code once you're done with it.


    Hope this helps.

Participate now!

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