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
200
-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, [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

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

[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,
Ron

[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

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]

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

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

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
> > 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
> ------------------------------------------------------------------------
> via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
> View this thread: http://www.excelforum.com/showthread...hreadid=507807
>
>

[via135]

thks RON!

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

-via135