Move Dash

[Guest]

We are outputing data (in csv format) from an AS400 accounting packing that
is to be imported in to Excel for the auditors. The RPG language in the AS400
reads/displays negative numbers with the dash on the rights side of the
number (123-) rather than the leading dash (-123) that we normally use in the
world.
Any tips for writting a module that will search thru range A1:A100 and move
the dash from the right to the left (or replace the right dash with brackets
on each side of the number? Can seem to get it right.
Thanks
Rick

 

[Guest]

Hi,
You could use following- add a working column (B) and insert the
following and copy down

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

With VBA:

Sub ConvertAS400()
Dim i As Integer
For i = 1 To 100
If Right(Range("a" & i), 1) = "-" Then
Range("A" & i) = ("-" & Left(Range("a" & i), Len(Range("a" & i)) - 1) * 1)
End If
Next i
End Sub
HTH

 

[Guest]

sub replace
dim cel as range
for each cel in activesheet.range("A1:A100")
if right(cel,1) = "-" then
cel = "-"+left(cel,len(cel)-1)
end if
next
end sub

untested but it should get you started

 

[Guest]

Thanks everyone, I'll try all solutions you've provided
Rick

 

[Peter81]

Rick,
Give this a try.

Sub Fix_Dash()
Dim n As Integer, Cell_Value As String, Cell_Temp As String, Dash_Place
As Integer

For n = 1 To 1000

Cell_Value = Range("A" & n).Value
Dash_Place = InStr(1, Cell_Value, "-")

If Dash_Place > 0 Then
Cell_Temp = "-" & Left(Cell_Value, Dash_Place - 1)
Range("A" & n).Value = Cell_Temp
End If

Next

End Sub

 

[Guest]

in normal practice of course i shouldn't have named the sub replace