+ Reply to Thread
Results 1 to 9 of 9

+ve and -ve values into 2 columns

  1. #1
    Registered User
    Join Date
    10-06-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    10

    +ve and -ve values into 2 columns

    Hi all,

    I have just changed banks and when I d/load the statements they come as a CSV file. The issue is the debits and credits are all in one column so when it is a credit it is a positive number ie. 2.98 but a debit is shown as a negative number ie -2.98. I was wanting to leave all the +ve values in one column and move all the -ve values to the next column so I can sum the columns for my accounts.
    Any help would be very well received.

    Thanks in advance

    Ali G

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: +ve and -ve values into 2 columns

    You can't just leave positive numbers where they are and move negative numbers ... except with VBA. You could use two separate formulae in Credit and Debit columns.

    For example, columns F and G could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and copied down. You could always hide column E.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-06-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: +ve and -ve values into 2 columns

    SO do you mean copy the one column so I have 2 columns the same

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: +ve and -ve values into 2 columns

    Just put the formulae in separate columns, and adjust the column reference (E) as necessary. Then copy down and see what you get. You could adjust the Debit column to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to change the sign.

  5. #5
    Registered User
    Join Date
    10-06-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: +ve and -ve values into 2 columns

    Yeah that works ...thanks

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: +ve and -ve values into 2 columns

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  7. #7
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: +ve and -ve values into 2 columns

    No formulas or VBA needed...

    If you want to remove the minus sign after the move, then select the entire column and use the Text To Columns dialog box (Data tab, Data Tools group)... Delimited, minus sign in Other field, Finish.

    If you want to retain the minus sign after the move, then select the entire column and use the Replace dialog box (CTRL+H) to replace the minus sign with, say, the vertical bar followed by a minus sign (|-) and then use Text To Columns as above, but use the vertical bar in the Other field instead.
    Last edited by Rick Rothstein; 08-15-2020 at 06:08 PM.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: +ve and -ve values into 2 columns

    Ah, so there IS a way to leave the Credits where they are AND move the Debits

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: +ve and -ve values into 2 columns

    Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Insert 4 Columns from Column E and fill repeated values in inserted columns
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2019, 10:18 PM
  2. Look up values in 2 adjacent columns. Return values in 3 columns if match.
    By Gssoc777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-04-2015, 12:28 PM
  3. [SOLVED] Copy/Paste multiple columns as values based on another columns criteria
    By Dgp2012 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-28-2013, 06:50 AM
  4. Locking columns based on calculated cell values in columns across a sheet.
    By andyr826208 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2012, 05:17 AM
  5. Replies: 0
    Last Post: 09-15-2012, 02:56 AM
  6. Replies: 2
    Last Post: 03-15-2012, 09:12 AM
  7. columns in excel to reflect array of values entered in previous two columns
    By netvasi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2011, 01:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1