Transform.

T

TOM

How can I transform this:

myVar = " 7.200" 'spaces are there

to

myNewVar = "7200"

Thanks
Tom
 
F

Frank Kabel

Hi
try
myVar=replace(myvar," ","")

or try
myvar=application.worksheetfunctions.trim(myvar)
 
T

Tom Ogilvy

Just to add to the excellent suggestion.

myVar = " 7.200"
? cdbl(myVar)
7.2

is a good thought, but unless it was a typo, it doesn't quite match the
requested results.

myNewVar = "7200"

perhaps

? cdbl(myvar)*1000
7200

with myNewVar dimensioned as String
 
T

Tom Ogilvy

or to assign to myNewVar

myNewVar = Application.substitute(application.Substitute(myVar,".",""),"
","")

or

myNewVar = Trim(application.Substitute(myVar,".",""))

myVar = " 7.200"
myNewVar = Trim(application.Substitute(myVar,".",""))
? myNewVar
7200
 
N

NickHK

Tom,
Doesn't that depend on the decimal separator ?

Nick

Tom Ogilvy said:
Just to add to the excellent suggestion.

myVar = " 7.200"
? cdbl(myVar)
7.2

is a good thought, but unless it was a typo, it doesn't quite match the
requested results.

myNewVar = "7200"

perhaps

? cdbl(myvar)*1000
7200

with myNewVar dimensioned as String
Click to expand...
 
F

Frank Kabel

Hi
in VBA always the dot is used as separator (regardless of your regional
settings)
 
T

Tom Ogilvy

He showed a period. Since he didn't state he wanted a turnkey internation
solution, then I guess not.

--
Regards,
Tom Ogilvy

NickHK said:
Tom,
Doesn't that depend on the decimal separator ?

Nick
Click to expand...
 
R

RJ KElly

HI you can try the following formula Iam sure it will work
you can substitute the RowColumn info for a variable it
will work the same, keep in mind those parenthesis are very
tricky PAY STRICT attention to them

=(TRUNC(C12)&(RIGHT((TRIM(C12)),(LEN(TRIM(C12))- FIND(".",
(TRIM(C12)))))))
 
Top