Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Can anyone help me validate a column to allow only text and spaces. No
punctuation. I used this below and was able to get only text but it also prevents spaces. =SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
This isn't thoroughly tested, but it seems to be working.....
Select cell A1 From the Excel main menu: <data<validation Allow: Custom Formula: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ "))) Note: In case text wrap impacts the display, there is only ONE space in that formula. It is located after the letter Z. Set the Error Alert and I *think* that does it. Does that help? *********** Regards, Ron XL2002, WinXP "Kimberly" wrote: Can anyone help me validate a column to allow only text and spaces. No punctuation. I used this below and was able to get only text but it also prevents spaces. =SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
DARN! I didn't copy enough characters from the formula!
It should be: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ ")))=0 (I had left off the "=0" part Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: This isn't thoroughly tested, but it seems to be working..... Select cell A1 From the Excel main menu: <data<validation Allow: Custom Formula: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ "))) Note: In case text wrap impacts the display, there is only ONE space in that formula. It is located after the letter Z. Set the Error Alert and I *think* that does it. Does that help? *********** Regards, Ron XL2002, WinXP "Kimberly" wrote: Can anyone help me validate a column to allow only text and spaces. No punctuation. I used this below and was able to get only text but it also prevents spaces. =SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Thank you - it does work! Now I'm just working on how to use it for an
entire column. Thank you!! "Ron Coderre" wrote: DARN! I didn't copy enough characters from the formula! It should be: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ ")))=0 (I had left off the "=0" part Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: This isn't thoroughly tested, but it seems to be working..... Select cell A1 From the Excel main menu: <data<validation Allow: Custom Formula: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ "))) Note: In case text wrap impacts the display, there is only ONE space in that formula. It is located after the letter Z. Set the Error Alert and I *think* that does it. Does that help? *********** Regards, Ron XL2002, WinXP "Kimberly" wrote: Can anyone help me validate a column to allow only text and spaces. No punctuation. I used this below and was able to get only text but it also prevents spaces. =SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
I can't figure it out for an entire column....do you have any suggestions?
"Ron Coderre" wrote: DARN! I didn't copy enough characters from the formula! It should be: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ ")))=0 (I had left off the "=0" part Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: This isn't thoroughly tested, but it seems to be working..... Select cell A1 From the Excel main menu: <data<validation Allow: Custom Formula: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ "))) Note: In case text wrap impacts the display, there is only ONE space in that formula. It is located after the letter Z. Set the Error Alert and I *think* that does it. Does that help? *********** Regards, Ron XL2002, WinXP "Kimberly" wrote: Can anyone help me validate a column to allow only text and spaces. No punctuation. I used this below and was able to get only text but it also prevents spaces. =SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
I'll assume you want to set the DV for cells A2:A100
Select A2:A100, with A2 as the active cell (this is important) From the Excel main menu: <data<validation Allow: Custom Formula: =SUMPRODUCT(--ISERROR(SEARCH(MID(A2,ROW($A$1:INDEX($A:$A,LEN(A2) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ ")))=0 Set the Error Alert and you're finished. NOTICE: Cell A2 is the active cell in the selected range AND the formula references cell A2. When you're done check the other cells...A4's DV formula references A4, A10's references A10, etc. Does that help? *********** Regards, Ron XL2002, WinXP "Kimberly" wrote: Thank you - it does work! Now I'm just working on how to use it for an entire column. Thank you!! "Ron Coderre" wrote: DARN! I didn't copy enough characters from the formula! It should be: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ ")))=0 (I had left off the "=0" part Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: This isn't thoroughly tested, but it seems to be working..... Select cell A1 From the Excel main menu: <data<validation Allow: Custom Formula: =SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1) ,1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ "))) Note: In case text wrap impacts the display, there is only ONE space in that formula. It is located after the letter Z. Set the Error Alert and I *think* that does it. Does that help? *********** Regards, Ron XL2002, WinXP "Kimberly" wrote: Can anyone help me validate a column to allow only text and spaces. No punctuation. I used this below and was able to get only text but it also prevents spaces. =SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
I can't seem to get that either. I'm still trying though. What do you think
about this code? Only prob is that I can't get it for the entire column. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo OuttaHere If Target.Address = "$F$4" Then '<<<< change cell Application.EnableEvents = False Dim strText As String Dim lngN As Long Const str_Chars As String = "[0-9a-zA-Z ]" strText = Target.Text For lngN = 1 To Len(strText) If Not Mid$(strText, lngN, 1) Like str_Chars Then MsgBox "Only numbers or alphabetic characters allowed. ", _ vbOKOnly, "Data Validation" Application.Undo Exit For End If Next 'lngN End If OuttaHe Application.EnableEvents = True End Sub "Kimberly" wrote: Can anyone help me validate a column to allow only text and spaces. No punctuation. I used this below and was able to get only text but it also prevents spaces. =SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation | Excel Worksheet Functions | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |