Need VBA to convert "5757-" text to "$ (5,757)"

Posted by Greg on February 13, 2002 8:55 AM

When importing a certain txt file, all negative numbers are displayed as a number with a trailing minus sign (i.e. 477676-) which Excel recognizes as text. I want a macro that searches through a sheet for these cells that have numbers with a trailing minus sign, and replaces the cell contents with the correct negative number in currency format like the following:

$ (477,676)

Posted by Jack in the UK on February 13, 2002 11:45 AM

Why VBA not format RANGE NAME [NT]

Posted by Greg on February 13, 2002 3:05 PM

Re: Why VBA not format RANGE NAME [NT]

The data is not in a consistant format nor is it in a nice clean table format. A macro that would work through a sheet (can be up to 500+ rows)and make the changes where necessary would be prefered. Not all cells are improperly formatted. The positive numbers are fine. It's just the occasional negative numbers that are the problem.

Posted by Barrie Davidson on February 13, 2002 5:47 PM

How about a formula instead of VBA?

Greg, why not put this formula in an adjacent column.

=IF(ISERROR(FIND("-",A1)),A1,-VALUE(LEFT(A1,LEN(A1)-1)))

You can then copy and paste values.

Will this work for you?
BarrieBarrie Davidson

Posted by Barrie Davidson on February 13, 2002 5:48 PM

Re: How about a formula instead of VBA? - better yet

This formula is smaller:

=IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),A1)

BarrieBarrie Davidson

Posted by Lucio on February 13, 2002 7:13 PM

Get "Dash Masher" from http://www.xl-logic.com/pages/vba.html

Posted by Greg on February 14, 2002 9:27 AM

Re: How about a formula instead of VBA? - better yet

Thanks for the input! But, I still think a macro would be my best bet. The raw data is in multiple columns and the insertion of more columns and formulas would be too time consuming. I have to go through this process on a monthly basis. Sticking a macro button in my toolbar that would automatically clean the data would be ideal.

Thanks again for those who contributed the formulas!

Posted by Greg on February 14, 2002 1:33 PM

Thanks Lucio!! Dash Masher was exactly what I wanted!!!!!!!!!

Posted by Jack in the UK on February 16, 2002 1:56 AM

Re: How about a formula instead of VBA? - better yet

Hi--

I understand the comments of command button fr easy, ive tested the formulas and they are spot on, i would go withthat - i can convert 65000 rows in a few seconds VBA would be hard pushed to beat that in much time saving as i would have to insert a column copnvert copy / paste as velue and remove and delete the original data colunm, add / delete takes excel in VBA the same time as manula, so little saving there as has to re calculate all formulas and absolute reference can play up and need re calculation once compleate.

Ill have a play and see if ican get a script up to do this by converting the given formula, or you could UDF in conditional formatting that row, which would be quicker..

just a few ideas.

HTH
Jack

Posted by Jack in the UK on February 16, 2002 2:14 AM

VBA As requested - this will do as you want.. Jack [NT]

>>>>>> Some code please put in modual and go from there, let me know if you need help..

Sub ConvertX()
' Written by Jack in the UK
'16th Feb 2002 MrExcel ....
'Add to command button or button in toolbar
'Edit ranges as reqd.

Range("D1:D500").Select
' Jack sets range to be converted.
With Selection
' Jack trips convertion
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC1,1)=""-"",-VALUE(LEFT(RC1,LEN(RC1)-1)),RC1)"
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With
' Jack compleats convertion
Range("D1").Select
' Jack selects first cell

End Sub

HTH
Rdgs
==========
Jack