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

"Rick K" wrote:

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

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
--
When you lose your mind, you free your life.


"Rick K" wrote:

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

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

"Rick K" wrote:

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

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


--
Peter81
------------------------------------------------------------------------
Peter81's Profile: http://www.excelforum.com/member.php...o&userid=25353
View this thread: http://www.excelforum.com/showthread...hreadid=388345

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

--
When you lose your mind, you free your life.


"ben" wrote:

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
--
When you lose your mind, you free your life.


"Rick K" wrote:

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