Results 1 to 5 of 5
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Data Conversion (2002 SP2)

    <P ID="edit" class=small>(Edited by pccoyle on 19-May-04 22:49. Edited to add sample worksheet)</P>I am importing data from our ERP system for uploading to our new system. We have placeholder inventory codes such as $0029 (denotes that this is $0.29 per metre fabric)
    When the data is opened in Excel, (I have tried both .dif and .csv) the $0029 code appears as $29, and the cell is formatted as currency. If I then format the cell to text, the number changes to 29, so I cannot do a search and replace as other columns may have the same number sequence as part of the data.
    How can I find a way, using Excel, to convert these incorrectly imported numbers to what they were originally.
    Attached is a small worksheet, the top row shows the data as it is imported, the second row shows how the data in column C should be.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

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

    Re: Data Conversion (2002 SP2)

    1- Select an empty cell and enter 100 into it.

    2- Select the cell again and then select Copy from the Edit menu.

    3- Select all of the cells that contain the incorrect values.

    4- Select "Paste Special" from the Edit menu.

    5- In the resulting dialog box put a tic mark next to Divide in the Operation section.

    6- Click OK.

    The values should now be correct.
    Legare Coleman

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Data Conversion (2002 SP2)

    Hey that's nifty, I will remember this. Unfortunately it does not do what I need, What shows as $29, format currency, I need to show as $0029, format text.
    Turning these cells into text just leaves 29 in the cell.
    Paul Coyle
    Approach love and cooking with reckless abandon

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

    Re: Data Conversion (2002 SP2)

    OK, then try this:

    Insert an empty column next to the one that needs converting.

    1- In the cell in that column in the same row as the first incorrect value enter the formula below:

    <pre>=TEXT(A1,"$0000")
    </pre>


    2- Fill this formula down the column as far as necessary.

    3- Select Cells from the Format menu and format the column as Text.

    4- Select the column with the formulas and then select Copy from the Edit menu.

    5- Select Paste Special from the Edit menu.

    6- In the resulting dialog box put a tic mark next to Values in the Paste section.

    7- Click OK.

    You should now have a column converted to text, and you can delete the original column.
    Legare Coleman

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Data Conversion (2002 SP2)

    Wonderful, works a treat! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Sort my worksheet to group these problem items, apply your formula and copy back the answer and the data is formatted as required.
    Thank you, and another Excel formula to add to the memory banks!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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