Script to remove the first and/or last character of string if they are a space

[Oliver Dewar]

Simple question (not sure if the script will be though!)

I need to remove any spaces that may appear either as the first or last character entered in a textbox on a userform.

I'll put the script into the exit event.

Note: the user may well not enter a space as the first or last character. This is just to clip the value back to words with only internal spaces so that the value can be cross referrenced against our database. At the moment a stray space will cause a duplicate to get past our checks and destroy the world (yes, the stakes are high on this one...)

Cheers!

 

[dave3009]

You can use trim, for example

Private Sub CommandButton1_Click()
Range("A1") = Trim(TextBox1.Value)
End Sub

Which will trim off and superfluous spaces at the beginning/end of the value in a textbox

 

[Richard Schollar]

If you want to replicate the action of the Excel worksheet function TRIM (which replaces multiple sequential instances of spaces within a string, then you can alternatively use the WorksheetFunction.Trim (it also removes leading/trailing spaces like the VBA function Trim that Dave used):

TextBox1.Value = Application.WorksheetFunction.Trim(TextBox1.Value)

 

[Oliver Dewar]

Perfect gentlemen.

Thanks for your help.

Regards

Oliver