converting text to negative numbers!

V

via135

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 "-" as negative
numbers such as

10
20
-300
-1110
-70

any help?

-via135
 
D

Dave Peterson

Dana DeLouis posted this:


Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, [email protected]
' = = = = = = = = = = = = = = = =
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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
R

Ron Coderre

In Excel 2002, you can do this:

Select the range you want converted from text to numbers
Data>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,
Ro
 
V

via135

sorry Dave!
i accept i am completely new to VB code!

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

thks!

-via135


Ron said:
In Excel 2002, you can do this:

Select the range you want converted from text to numbers
Data>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,
Ro
Click to expand...
 
V

via135

remainding again for help???!!!

-via135



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 "numbers" and "numbers formatted in text" as given in
my example (100- , 200- , 300-). i want only those numbers to be
converted to negative numbers!

thks!

-via135
Click to expand...
 
R

Ron Coderre

I wasn't sure from your post whether you'd solved the problem or not.
i am getting a mix _repeat_ mix of "numbers" and "numbers formatted in text" <
Click to expand...

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-Pro


via135 said:
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 "numbers" and "numbers formatted in text" as given in
my example (100- , 200- , 300-). i want only those numbers to be
converted to negative numbers!

thks!

-via135


Ron said:
In Excel 2002, you can do this:

Select the range you want converted from text to numbers
Data>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
Click to expand...
Click to expand...
 
V

via135

thks RON!

i never thought the TTC will take care of the trailing "minus" also!!!
problem solved!!
thks!

-via135
 
Top