software

hi all!

while importing data from .dat file, i have a column of data mixed with
numbers and text (actually they are negative numbers in the original
.dat file) as under:

10
200
300-
1110-
70-

i want to convert the text (numbers) ending with quot;-quot; as negative
numbers such as

10
20
-300
-1110
-70

any help?

-via135--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=507807Dana DeLouis posted this:Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis,
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim bigrng As Range

On Error Resume Next
Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If bigrng Is Nothing Then Exit Sub

For Each rng In bigrng.Cells
rng = CDbl(rng)
Next
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm

via135 wrote:
gt;
gt; hi all!
gt;
gt; while importing data from .dat file, i have a column of data mixed with
gt; numbers and text (actually they are negative numbers in the original
gt; dat file) as under:
gt;
gt; 10
gt; 200
gt; 300-
gt; 1110-
gt; 70-
gt;
gt; i want to convert the text (numbers) ending with quot;-quot; as negative
gt; numbers such as
gt;
gt; 10
gt; 20
gt; -300
gt; -1110
gt; -70
gt;
gt; any help?
gt;
gt; -via135
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=507807

--

Dave Peterson


In Excel 2002, you can do this:

Select the range you want converted from text to numbers
Datagt;Text-to-Columns
Click the [Next] button twice
Click the [Advanced..] button (on Step 3 of 3)
Check: Trailing minus for negative numbers
Click the [OK] button
Click the [Finish] button

Something you can use?

Regards,
Ron--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: www.excelforum.com/member.php...oamp;userid=21419
View this thread: www.excelforum.com/showthread...hreadid=507807
sorry Dave!
i accept i am completely new to VB code!

yes RON..! while importing from a .dat file, i am getting a mix
_repeat_ mix of quot;numbersquot; and quot;numbers formatted in textquot; as given in
my example (100- , 200- , 300-). i want only those numbers to be
converted to negative numbers!

thks!

-via135Ron Coderre Wrote:
gt; In Excel 2002, you can do this:
gt;
gt; Select the range you want converted from text to numbers
gt; Datagt;Text-to-Columns
gt; Click the [Next] button twice
gt; Click the [Advanced..] button (on Step 3 of 3)
gt; Check: Trailing minus for negative numbers
gt; Click the [OK] button
gt; Click the [Finish] button
gt;
gt; Something you can use?
gt;
gt; Regards,
gt; Ron--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=507807
remainding again for help???!!!

-via135

via135 Wrote:
gt; sorry Dave!
gt; i accept i am completely new to VB code!
gt;
gt; yes RON..! while importing from a .dat file, i am getting a mix
gt; _repeat_ mix of quot;numbersquot; and quot;numbers formatted in textquot; as given in
gt; my example (100- , 200- , 300-). i want only those numbers to be
gt; converted to negative numbers!
gt;
gt; thks!
gt;
gt; -via135--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=507807I wasn't sure from your post whether you'd solved the problem or not.

gt;i am getting a mix _repeat_ mix of quot;numbersquot; and quot;numbers formatted in textquot; lt;

You should be able to use the Text_to_Columns solution that I posted on the
whole column range that includes numbers and numbers formatted as text. The
numeric values will be unaffected while the numbers formatted as text
(including those with trailing minus signs) will be properly converted to
numeric values.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;via135quot; wrote:

gt;
gt; sorry Dave!
gt; i accept i am completely new to VB code!
gt;
gt; yes RON..! while importing from a .dat file, i am getting a mix
gt; _repeat_ mix of quot;numbersquot; and quot;numbers formatted in textquot; as given in
gt; my example (100- , 200- , 300-). i want only those numbers to be
gt; converted to negative numbers!
gt;
gt; thks!
gt;
gt; -via135
gt;
gt;
gt; Ron Coderre Wrote:
gt; gt; In Excel 2002, you can do this:
gt; gt;
gt; gt; Select the range you want converted from text to numbers
gt; gt; Datagt;Text-to-Columns
gt; gt; Click the [Next] button twice
gt; gt; Click the [Advanced..] button (on Step 3 of 3)
gt; gt; Check: Trailing minus for negative numbers
gt; gt; Click the [OK] button
gt; gt; Click the [Finish] button
gt; gt;
gt; gt; Something you can use?
gt; gt;
gt; gt; Regards,
gt; gt; Ron
gt;
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=507807
gt;
gt;


thks RON!

i never thought the TTC will take care of the trailing quot;minusquot; also!!!
problem solved!!
thks!

-via135--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=507807