Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
challenge coverting to values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge: Return the rightmost 12 values (rolling 12 months) | Excel Worksheet Functions | |||
Challenge: Unique Values via Formulas | Excel Discussion (Misc queries) | |||
Coverting seconds into hr:min:sec | Excel Discussion (Misc queries) | |||
Coverting time | Excel Worksheet Functions | |||
Coverting a VBA array from 2-D to 1-D | Excel Programming |