Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cell format problem (2000)

    When I concatenate an address line into street number, name and suburb the cells that have and unit no/street number ie 1/2
    in the concatenated celss the number will often (but not always be converted to a date eg 01-Feb for above eg.
    I have tried changing the formats of the original address line cells and the new cells before and after concatenating them. Ihave tried different format types - general and text. I have tried putting similar data into the cell the street number will go to before hand and ensuring it is a general or text format - nothing works - do you have any suggestions?
    cheers Mary

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell format problem (2000)

    If you enter 1/2 into a cell that has the General format, that will be interpreted as the date 1/2/2004 (where 2004 is the current year. Once a value in a cell has been interpreted as a number or a date, changing the format of the cell to text will not change the value in the cell to text, it will stay as either a date or a number.

    It sounds like someone entered the values into the cells and then formatted the cell with the format d/m to get it to display like it was 1/2 but the value in the cell is really a date. To work properly, the cells should be formatted to Text before the values are entered into them. If you have a large number of these values already entered, then the procedure below can be used to fix the problem:

    1- Insert an empty column next to the column that contains these values.

    2- Enter the formula below into the top cell of the inserted column, replacing each reference to A1 with the first cell in the column containing the unit no/street number:

    <pre>=IF(ISTEXT(A1),A1,TEXT(A1,"d/m"))
    </pre>


    You may need to reverse "d/m" to "m/d" depending you what date format your system uses.

    3- Copy this formula down the column as far as the values in the unit no/street number column go.

    4- Select the column containing these formula and format all of the cells as Text.

    5- Select Copy from the Edit menu to copy the column.

    6- Select "Paste Special" from the Edit menu. In the resulting dialog box select Values in the Paste section. Click OK.

    You should now have a new column with all of the values converted to Text. If this is correct, you can now delete the original column.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell format problem (2000)

    To Legare Coleman
    Thank you soooo much - it works perfectly
    cheers
    Mary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •