converting text to negative numbers!

[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

 

[Dave Peterson]

Dana DeLouis posted this:


Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, (e-mail address removed)
' = = = = = = = = = = = = = = = =
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

 

[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

 

[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

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

 

[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

 

[Guest]

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

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

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

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

 

[via135]

thks RON!

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

-via135