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 |
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 |
All times are GMT +1. The time now is 05:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com