challenge coverting to values

S

Shane

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
 
N

Norman Jones

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