I often get receive data in the form 100- which tells me that the value
is negative 100.

In order to use this data properly i need to have the '-' at the front
so it can be treated as a number.

Can anyone tell me how i can create a macro to do this - it would be
much appreciated


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Jack,

This code does it for the active cell. Just adapt to the range of cells
applicable

With ActiveCell
.Value = Right(.Value, 1) & Left(.Value, Len(.Value) - 1)
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jack27" wrote in message
...

I often get receive data in the form 100- which tells me that the value
is negative 100.

In order to use this data properly i need to have the '-' at the front
so it can be treated as a number.

Can anyone tell me how i can create a macro to do this - it would be
much appreciated


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

With your data in A1 enter into B1 =LEFT(A1,LEN(A1)-1)*-1
and copy down then Copy Col B to Col B using Paste-Special -Values, OK
HTH

"jack27" wrote in message
...

I often get receive data in the form 100- which tells me that the value
is negative 100.

In order to use this data properly i need to have the '-' at the front
so it can be treated as a number.

Can anyone tell me how i can create a macro to do this - it would be
much appreciated


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Take a look he

http://www.mcgimpsey.com/excel/postfixnegatives.html

In article ,
jack27 wrote:

I often get receive data in the form 100- which tells me that the value
is negative 100.

In order to use this data properly i need to have the '-' at the front
so it can be treated as a number.

Can anyone tell me how i can create a macro to do this - it would be
much appreciated

On Sun, 7 Dec 2003 08:25:21 -0600, jack27
wrote:


I often get receive data in the form 100- which tells me that the value
is negative 100.

In order to use this data properly i need to have the '-' at the front
so it can be treated as a number.

Can anyone tell me how i can create a macro to do this - it would be
much appreciated



If you have Excel 2002, you can use the Data/Text to Columns wizard


--ron