Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change "-" from right first character to left first character to make a negative amount
Hi
I have a column of data. Unfortunately the negative symbol is on the right of the numbers to represent a negative number. What I need is a macro to look down a column and if it find the first character to the right of that cell is a "-" symbol then move that to the first character on the left of the cell to make the numbers negative. I hope this is possible and someone can help, Thank you so much in advance, Andrea |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change "-" from right first character to left first character to m
I believe you can use Text-to-Columns if you have Excel 2000 or later.
Try this: Select the column range of cells to be impacted From the Excel main menu: <data<text-to-columns .....click [Next] until Step 3 of 3 Click the [Advanced] button .....Check: "Trailing minus sign for negative numbers"....Click [OK] Click [Finish] Does that help? *********** Regards, Ron XL2003, WinXP " wrote: Hi I have a column of data. Unfortunately the negative symbol is on the right of the numbers to represent a negative number. What I need is a macro to look down a column and if it find the first character to the right of that cell is a "-" symbol then move that to the first character on the left of the cell to make the numbers negative. I hope this is possible and someone can help, Thank you so much in advance, Andrea |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change "-" from right first character to left first character to m
Here is one VBA solution that ignores values unless they contain a dash (minus sign). Using this method, you need to make your selection first, then run. Watch for line wrapping: Dim lDash As Long Dim sValue As String Dim rCell As Range For Each rCell In Selection If InStr(1, rCell.FormulaR1C1, "-", vbTextCompare) < 0 Then lDash = InStr(1, rCell.FormulaR1C1, "-", vbTextCompare) sValue = Application.WorksheetFunction.Replace(rCell.Formul aR1C1, lDash, 1, "") rCell.FormulaR1C1 = "-" & sValue End If Next rCell HTH " wrote: Hi I have a column of data. Unfortunately the negative symbol is on the right of the numbers to represent a negative number. What I need is a macro to look down a column and if it find the first character to the right of that cell is a "-" symbol then move that to the first character on the left of the cell to make the numbers negative. I hope this is possible and someone can help, Thank you so much in advance, Andrea |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If 5th character="-" delete everything to the left otherwise do no | Excel Worksheet Functions | |||
How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric? | Excel Worksheet Functions | |||
I need to count occurr. of the character "," in a single cell | Excel Worksheet Functions | |||
error: "invalid character in text conent" How do I open this file | Excel Discussion (Misc queries) | |||
Change dollar amount to a negative if another colum has a "P" | Excel Programming |