I need to write a script, that amonst other things will do the following.

GoTo a certain cell
Capture the contents (ie text)
Remove the trailing character (a single space)
Past the new contents (ie text)
Move on to another cell.

eg
capture "$122.00 " [ <- note space character ]
paste "$122.00" [ <- note NO space character ]
move to next cell
capture "$212122.00 " [ <- note space character ]
paste "$212122.00" [ <- note NO space character ]
move to next cell
etc etc

Moving is ok
Capture is ok
Paste is ok

Need help for the deleting of the " " character [space character]

OK, an easy one, but any tips, or code, please.
Thank You VB Gurus.

 

string = RTrim(string) - if you only want the space from the right hand side taken off, or

string = Trim(string) - if you want spaces from either end taken off.

Cheers

Steve

 

String = Left(String,Len(String)-1) if you want to remove the last character even if it isn't a space

 

Thanks Guys, I shall try those today.

 

Works very well, very slick !! Thanks.

Thinking laterally now, say I have a cell with a full name in the following syntax [firstname lastname], ie with a space as the separator,
can I use VB (or excel) to separate names.

ie paste firstname into row B
paste surname into row C

The crux is that there maybe between 3 to 12 characters for the firstname and surnames, or any combination thereoff.

Any Thoughts ??

 

You could use something like this: [firstname] [lastname] in A1
in B1: = LEFT(A1, FIND(" ",A1,1) ------ finds the space and takes everthing to the left
in C1: = RIGHT(A1, LEN(A1)-FIND(" ",A1,1)) ----- finds the space and takes it's location away from the length of A1

HTH

Mark

 

This works very nicely, too.
I think I need to learn about the 'find' command in excel, I've never used it til now.

So if I wanted to do exactly the same operations but using VB for excel, would the syntax be slightly different ?

 

Syntax is a bit different mainly 'cos of InStr instead of FIND

Dim strName As String

Range("A1").Select
strName = Range("A1").Value


Cells(1, 2) = Left(strName, InStr(1, strName, " ") - 1)
Cells(1, 3) = Right(strName, Len(strName) - InStr(1, strName, " "))

This can, of course, be modified to suit your needs

HTH
Mark