challenge coverting to values
Hi Shane,
Manually you can do a series of Edit/Replace's, but remember to use an
intermediate replacement character for your commas (or periods)!
If you do this on a regular basis and want a programming solution try:
Sub Tester9()
With Selection
.Replace What:=".", Replacement:="#", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="aud", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="#", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Value = .Value
End Sub
You can, of course, replace "Selection" with your range name or addresss as
appropriate.
---
Regards,
Norman
"Shane" wrote in message
...
Hi all,
i have a situatuion where the data extracted out of systems is as follows.
2.390,06 AUD and $ 1.816,43
Note, the "." is in place of thousands and the "," is in place of a
decimal.
How can i convert these text feilds to numbers and exclude the AUD text
and
"$" text and also convert the thousands separater to "," and have a
decimal
as "."
Thanks
SK
|