i have one columns of numbers which displays negative numbers in this format,
300-, this is after import from legacy conversion program. In Excel, how can
i convert this negative format from 999- to -999.


Tks !

hi!

select the range

goto
Data Text to Col Next Next Advanced
check "Trailing minus for negative numbers" ok


-via135


pk Wrote:
i have one columns of numbers which displays negative numbers in this
format,
300-, this is after import from legacy conversion program. In Excel,
how can
i convert this negative format from 999- to -999.


Tks !


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=522960

I'm not sure how you are importing this because excel has automatic
detection of trailing minus signs and converts them to normal numbers

If the numbers are in one column try highlighting it then use
Text To Columns
Step through the wizard (Click NEXT twice)
Notice the 'Advanced' button - click on that and make sure that the
'Trailing minus for negative numbers' is ticked on
Then click OK and Finish

Hope it helps
George


pk wrote:
i have one columns of numbers which displays negative numbers in this format,
300-, this is after import from legacy conversion program. In Excel, how can
i convert this negative format from 999- to -999.


Tks !

Hi George, via135;

Tks for the respond. However, i dun see 'Trailing minus for negative
numbers' in the 'Advanced' button' in either Office 2000 or Office 2003 :(

"George" wrote:

I'm not sure how you are importing this because excel has automatic
detection of trailing minus signs and converts them to normal numbers

If the numbers are in one column try highlighting it then use
Text To Columns
Step through the wizard (Click NEXT twice)
Notice the 'Advanced' button - click on that and make sure that the
'Trailing minus for negative numbers' is ticked on
Then click OK and Finish

Hope it helps
George


pk wrote:
i have one columns of numbers which displays negative numbers in this format,
300-, this is after import from legacy conversion program. In Excel, how can
i convert this negative format from 999- to -999.


Tks !

I'm using office XP (2002) and its a check box under the advanced button
Very unusual for it not to be in 2000 and 2003 and to be in 2002(XP)
Can anyone else confirm any of this.

When you click on the advanced button;
There should be 2 combo boxes with the ability to choose the decimal
separator and the thousands separator. You should also see a check box
and 3 buttons (RESET, OK, CANCEL)

What do you get when you click the advanced button?

George


pk wrote:
Hi George, via135;

Tks for the respond. However, i dun see 'Trailing minus for negative
numbers' in the 'Advanced' button' in either Office 2000 or Office 2003 :(

"George" wrote:


I'm not sure how you are importing this because excel has automatic
detection of trailing minus signs and converts them to normal numbers

If the numbers are in one column try highlighting it then use
Text To Columns
Step through the wizard (Click NEXT twice)
Notice the 'Advanced' button - click on that and make sure that the
'Trailing minus for negative numbers' is ticked on
Then click OK and Finish

Hope it helps
George


pk wrote:

i have one columns of numbers which displays negative numbers in this format,
300-, this is after import from legacy conversion program. In Excel, how can
i convert this negative format from 999- to -999.


Tks !

I am using 2000 and I looked for that option, but did not see it. Never
looked for it before (I did see the advanced button, but not the trailing
negative sign option. Only decimal and thousand seperators IIRC.)

However, I tried this formula, and it seemed to work on my test data:

=IF(RIGHT(A3,1)="-",VALUE(LEFT(A3, LEN(A3) - 1)) * -1, A3)

--
Kevin Vaughn


"George" wrote:

I'm using office XP (2002) and its a check box under the advanced button
Very unusual for it not to be in 2000 and 2003 and to be in 2002(XP)
Can anyone else confirm any of this.

When you click on the advanced button;
There should be 2 combo boxes with the ability to choose the decimal
separator and the thousands separator. You should also see a check box
and 3 buttons (RESET, OK, CANCEL)

What do you get when you click the advanced button?

George


pk wrote:
Hi George, via135;

Tks for the respond. However, i dun see 'Trailing minus for negative
numbers' in the 'Advanced' button' in either Office 2000 or Office 2003 :(

"George" wrote:


I'm not sure how you are importing this because excel has automatic
detection of trailing minus signs and converts them to normal numbers

If the numbers are in one column try highlighting it then use
Text To Columns
Step through the wizard (Click NEXT twice)
Notice the 'Advanced' button - click on that and make sure that the
'Trailing minus for negative numbers' is ticked on
Then click OK and Finish

Hope it helps
George


pk wrote:

i have one columns of numbers which displays negative numbers in this format,
300-, this is after import from legacy conversion program. In Excel, how can
i convert this negative format from 999- to -999.


Tks !