Data Validation, Need text any length no spaces
 

I'd like to know if anyone knows how to set up data validation so that
a user is forced to enter text without spaces. For example:

J 23 45 should be J2345

I'm not concerned about placing restrictions on the length, just that
there are no spaces in the text entered.

Any help someone could offer would be appreciated.

Thanks,
BB

Data Validation, Need text any length no spaces
 

A very simple worksheet change macro will do the trick

sub worksheet_change(Target as range)

target.value = trim(target.value)

end sub

this will only work on one worksheet. Go to tab on bottom of worksheet
(normally sheet1) and right click. Select view code. Place the 3 lines
above in VBA window. You may want ot restrict the trimming of spaces to only
a range of cells. Then use this

sub worksheet_change(Target as range)

if (target.row = 5) and (target.row <= 10) and _
(target.column = 3) and (target.column <= 20) then

target.value = trim(target.value)
end if

end sub


"BB" wrote:

I'd like to know if anyone knows how to set up data validation so that
a user is forced to enter text without spaces. For example:

J 23 45 should be J2345

I'm not concerned about placing restrictions on the length, just that
there are no spaces in the text entered.

Any help someone could offer would be appreciated.

Thanks,
BB

Data Validation, Need text any length no spaces
 

Joel, the Trim() only removes leading/trailing whitespace, but wouldn't get
rid of any in the middle such as would be in an entry like "abc def ghi ".

BB, Joel has shown one way to restrict the area you test for, here are a
couple of others with the use of Split() to remove all spaces within the
entry; those on either end and any in the middle of it:

First, if you need to just examine 1 cell (change $A$1 as needed):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmpResult As Variant
Dim finalResult As String
Dim sCount As Long

If Target.Address < "$A$1" Then
Exit Sub ' not a change in A1
End If
'just fix it
If InStr(Target, " ") Then
tmpResult = Split(Target, " ")
For sCount = LBound(tmpResult) To UBound(tmpResult)
finalResult = finalResult & tmpResult(sCount)
Next
Target.Value = finalResult
End If
End Sub

Or if you need a whole column tested (A in this example, adjust as needed):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmpResult As Variant
Dim finalResult As String
Dim sCount As Long
'for any cell in column A
If Application.Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub ' not a change in A1
End If
'just fix it
If InStr(Target, " ") Then
tmpResult = Split(Target, " ")
For sCount = LBound(tmpResult) To UBound(tmpResult)
finalResult = finalResult & tmpResult(sCount)
Next
Target.Value = finalResult
End If
End Sub

To explain Split() a little, it returns an array of the various pieces it
found and you have to put that back together. By declaring tmpResult as a
Variant, it allows the code to set the dimension it needs for the array at
run time.



"Joel" wrote:

A very simple worksheet change macro will do the trick

sub worksheet_change(Target as range)

target.value = trim(target.value)

end sub

this will only work on one worksheet. Go to tab on bottom of worksheet
(normally sheet1) and right click. Select view code. Place the 3 lines
above in VBA window. You may want ot restrict the trimming of spaces to only
a range of cells. Then use this

sub worksheet_change(Target as range)

if (target.row = 5) and (target.row <= 10) and _
(target.column = 3) and (target.column <= 20) then

target.value = trim(target.value)
end if

end sub


"BB" wrote:

I'd like to know if anyone knows how to set up data validation so that
a user is forced to enter text without spaces. For example:

J 23 45 should be J2345

I'm not concerned about placing restrictions on the length, just that
there are no spaces in the text entered.

Any help someone could offer would be appreciated.

Thanks,
BB

Data Validation, Need text any length no spaces
 

KISS

sub worksheet_change(Target as range)

target.value = replace(target.value," ","")

end sub

"JLatham" wrote:

Joel, the Trim() only removes leading/trailing whitespace, but wouldn't get
rid of any in the middle such as would be in an entry like "abc def ghi ".

BB, Joel has shown one way to restrict the area you test for, here are a
couple of others with the use of Split() to remove all spaces within the
entry; those on either end and any in the middle of it:

First, if you need to just examine 1 cell (change $A$1 as needed):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmpResult As Variant
Dim finalResult As String
Dim sCount As Long

If Target.Address < "$A$1" Then
Exit Sub ' not a change in A1
End If
'just fix it
If InStr(Target, " ") Then
tmpResult = Split(Target, " ")
For sCount = LBound(tmpResult) To UBound(tmpResult)
finalResult = finalResult & tmpResult(sCount)
Next
Target.Value = finalResult
End If
End Sub

Or if you need a whole column tested (A in this example, adjust as needed):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmpResult As Variant
Dim finalResult As String
Dim sCount As Long
'for any cell in column A
If Application.Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub ' not a change in A1
End If
'just fix it
If InStr(Target, " ") Then
tmpResult = Split(Target, " ")
For sCount = LBound(tmpResult) To UBound(tmpResult)
finalResult = finalResult & tmpResult(sCount)
Next
Target.Value = finalResult
End If
End Sub

To explain Split() a little, it returns an array of the various pieces it
found and you have to put that back together. By declaring tmpResult as a
Variant, it allows the code to set the dimension it needs for the array at
run time.



"Joel" wrote:

A very simple worksheet change macro will do the trick

sub worksheet_change(Target as range)

target.value = trim(target.value)

end sub

this will only work on one worksheet. Go to tab on bottom of worksheet
(normally sheet1) and right click. Select view code. Place the 3 lines
above in VBA window. You may want ot restrict the trimming of spaces to only
a range of cells. Then use this

sub worksheet_change(Target as range)

if (target.row = 5) and (target.row <= 10) and _
(target.column = 3) and (target.column <= 20) then

target.value = trim(target.value)
end if

end sub


"BB" wrote:

I'd like to know if anyone knows how to set up data validation so that
a user is forced to enter text without spaces. For example:

J 23 45 should be J2345

I'm not concerned about placing restrictions on the length, just that
there are no spaces in the text entered.

Any help someone could offer would be appreciated.

Thanks,
BB

Data Validation, Need text any length no spaces
 

You can use data validation.
As validation criteria use a custom formula:
=ISERROR(FIND(" ",A1))

Above formula results in TRUE if the input value of A1 ontains no spaces.
(The FIND tries to find a space, finds none and results in error. ISERROR
results in TRUE = input is valid)

Cheers,

Joerg Mochikun


"BB" wrote in message
oups.com...
I'd like to know if anyone knows how to set up data validation so that
a user is forced to enter text without spaces. For example:

J 23 45 should be J2345

I'm not concerned about placing restrictions on the length, just that
there are no spaces in the text entered.

Any help someone could offer would be appreciated.

Thanks,
BB

Data Validation, Need text any length no spaces
 

EXCELLENT!!

"Joel" wrote:

KISS

sub worksheet_change(Target as range)

target.value = replace(target.value," ","")

end sub

"JLatham" wrote:

Joel, the Trim() only removes leading/trailing whitespace, but wouldn't get
rid of any in the middle such as would be in an entry like "abc def ghi ".

BB, Joel has shown one way to restrict the area you test for, here are a
couple of others with the use of Split() to remove all spaces within the
entry; those on either end and any in the middle of it:

First, if you need to just examine 1 cell (change $A$1 as needed):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmpResult As Variant
Dim finalResult As String
Dim sCount As Long

If Target.Address < "$A$1" Then
Exit Sub ' not a change in A1
End If
'just fix it
If InStr(Target, " ") Then
tmpResult = Split(Target, " ")
For sCount = LBound(tmpResult) To UBound(tmpResult)
finalResult = finalResult & tmpResult(sCount)
Next
Target.Value = finalResult
End If
End Sub

Or if you need a whole column tested (A in this example, adjust as needed):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmpResult As Variant
Dim finalResult As String
Dim sCount As Long
'for any cell in column A
If Application.Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub ' not a change in A1
End If
'just fix it
If InStr(Target, " ") Then
tmpResult = Split(Target, " ")
For sCount = LBound(tmpResult) To UBound(tmpResult)
finalResult = finalResult & tmpResult(sCount)
Next
Target.Value = finalResult
End If
End Sub

To explain Split() a little, it returns an array of the various pieces it
found and you have to put that back together. By declaring tmpResult as a
Variant, it allows the code to set the dimension it needs for the array at
run time.



"Joel" wrote:

A very simple worksheet change macro will do the trick

sub worksheet_change(Target as range)

target.value = trim(target.value)

end sub

this will only work on one worksheet. Go to tab on bottom of worksheet
(normally sheet1) and right click. Select view code. Place the 3 lines
above in VBA window. You may want ot restrict the trimming of spaces to only
a range of cells. Then use this

sub worksheet_change(Target as range)

if (target.row = 5) and (target.row <= 10) and _
(target.column = 3) and (target.column <= 20) then

target.value = trim(target.value)
end if

end sub


"BB" wrote:

I'd like to know if anyone knows how to set up data validation so that
a user is forced to enter text without spaces. For example:

J 23 45 should be J2345

I'm not concerned about placing restrictions on the length, just that
there are no spaces in the text entered.

Any help someone could offer would be appreciated.

Thanks,
BB