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


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

Dana 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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

via135 wrote:

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

--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://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
DataText-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: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://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 "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 Coderre Wrote:
In Excel 2002, you can do this:

Select the range you want converted from text to numbers
DataText-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
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=507807

remainding again for help???!!!

-via135




via135 Wrote:
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


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

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


"via135" wrote:


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 Coderre Wrote:
In Excel 2002, you can do this:

Select the range you want converted from text to numbers
DataText-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
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=507807

thks RON!

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

-via135


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