Move Dash

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
Back
Top