Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving a character (2003)

    I have a spreadsheet in which the negative numbers have been imported from a system called Jano with the subtraction symbol after the number. I want to move the symbol to the beginning of the number. The way I have it now, I am able to extract the subtraction symbol after it with this function: =IF(ISNUMBER(SEARCH("-",C5,3)),RIGHT(C5,1),C5) and then concatenate it back with the original number: =IF(ISNUMBER(SEARCH("-",D5)),CONCATENATE(D5,C5),C5)
    My problem is that it is putting a subtraction symbol at the beginnning but it's also keeping the one at the end which messes up the subtotal feature I'm trying to use. Thanks to anyone who can help with this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving a character (2003)

    Are the imported numbers really numbers, or are they text values that look like numbers?
    If the former, it's probably only a matter of changing the number format in Format | Cells...
    If the latter, you can use

    =IF(ISNUMBER(SEARCH("-",D5)),CONCATENATE(D5,LEFT(C5,LEN(C5)-1)),C5)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving a character (2003)

    Hi There

    A similar one to yours seems to work:

    =IF(ISNUMBER(SEARCH("-",D5)),CONCATENATE(RIGHT(D5,1),LEFT(D5,LEN(D5)-1)),D5)
    Jerry

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

    Re: Moving a character (2003)

    Try this:

    <code>
    =IF(RIGHT(D5,1)="-",VALUE("-"&LEFT(D5,LEN(D5)-1)),D5)
    </code>
    Legare Coleman

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

    Re: Moving a character (2003)

    Hans: Your formula results in a #VALUE error for me. Even if it didn't, it would result in a string, not s number that could be used with Subtotal.
    Legare Coleman

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

    Re: Moving a character (2003)

    Jerry: Your formula results in a string, not a number that can be used with Subtotal.
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving a character (2003)

    My formula just built on the original posters implicit information that the original value is in C5 and that D5 contains either "-" or the (positive) value from C5.
    Your remark about the result being a string instead of a number is correct.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving a character (2003)

    Thanks Legare, I realised my problem when you were posting your shorter version, I was going to add this:

    =IF(ISNUMBER(SEARCH("-",D5)),CONCATENATE(RIGHT(D5,1),LEFT(D5,LEN(D5)-1))*1,D5)

    Where I multiplied the contatenate by 1 to convert it but it was paled into insignicance by your offering using value <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

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

    Re: Moving a character (2003)

    You may be correct. From the original post, I assumed that D5 contained the number followed by a minus sign as a single string. When I tested your formula, I missed the C5 in the formula and just had "1234-" in D5. C5 was empty, and that would have caused the #VALUE error. Maybe knichols can get something that works from the three posts.
    Legare Coleman

  10. #10
    New Lounger
    Join Date
    Nov 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a character (2003)

    Thanks so much. It did exactly what I wanted. My only problem seems to be the fact that the negative numbers appear to be text even though I've changed their formats so of course the subtotals aren't working. Thank you anyway for getting me this far.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving a character (2003)

    If you use this variation on Legare's formula
    <code>
    =VALUE(IF(RIGHT(C5,1) ="-","-"&LEFT(C5,LEN(C5)-1),C5))
    </code>
    you should get a number value regardless of whether the original value is text or number, and of whether the - is at the beginning or at the end. See the attached sample workbook.
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Nov 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a character (2003)

    I love you!!! That worked like a charm. Thanks so much.

Posting Permissions

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