Changing a positive or negative value based on the input's suffix, then removing suffix

[Theredwind]

Hi, first time posting so I hope this is in the right place pertaining to coding

I need to change the format of a coordinate, by removing the suffix ("125.00S", where S is for South) and then changing positive/negative value based on said removed suffix (N would keep the value as positive, S would change the value to negative).

in short
Input cell at A1 would read: 125.00S
I want Output cell at A2 to read: -125.00

I know I can remove the S from the string by using =Left(A1,LEN(A1)-1)
but I don't know how to use that suffix to change the number to a negative or positive value. Ideally, I would like to do this in one cell without having to use other cells.

thanks in advance.

[martindwilson]

=IF(RIGHT(A1)="S",SUBSTITUTE(A1,"S","")*-1,SUBSTITUTE(A1,"N","")*1)
then format cell to 2 decimal places

[Theredwind]

well first off, that was super fast!!

second off, that was a completely different approach than what I had...

Thanks for both of those of things! very greatly appreciated

[Ace_XL]

Couple of other ways

=IF(RIGHT(A1)="S",-1,1)*SUBSTITUTE(A1,RIGHT(A1),"")
or
=IF(RIGHT(A1)="S",-1,1)*LEFT(A1,LEN(A1)-1)

[Theredwind]

heh.. multiple ways to do the same thing... thanks Ace_XL

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.