# Thread: Moving a character (2003)

1. ## 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. ## 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. ## 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)

4. ## Re: Moving a character (2003)

Try this:

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

5. ## 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.

6. ## Re: Moving a character (2003)

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

7. ## 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.

8. ## 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>

9. ## 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.

10. ## 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. ## 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.

12. ## 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
•