Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Hi there, I have a spreadsheet containing first & last names (columns A & B),
up to 10,000 rows and some of the names contain 'foreign' characters - such as é. I would like to be able to hightlight all cells that contain a non-standard character (ie: A-Z, a-z, 0-9) which will allow our admin team to eailly filter and correct before the data is loaded into the system. I've been trying to create a macro for this but I can't seem to get a formula together to include for all the possible special characters. I'm ok with excel but by no means a whizz, if anyone can offer any advice I would be most grateful. Best regards, Gareth |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Try this UDF:
Function standchr(instring) standchr = True For i = 1 To Len(instring) If Not (LCase(Mid(instring, i, 1)) = "a" And LCase(Mid(instring, i, 1)) <= "z") Then standchr = False Exit For End If Next i End Function Apply conditional formatting with formula =NOT( standchr(A1)) -- Regards! Stefi Gareth_Evans (InterCall EMEA) ezt *rta: Hi there, I have a spreadsheet containing first & last names (columns A & B), up to 10,000 rows and some of the names contain 'foreign' characters - such as é. I would like to be able to hightlight all cells that contain a non-standard character (ie: A-Z, a-z, 0-9) which will allow our admin team to eailly filter and correct before the data is loaded into the system. I've been trying to create a macro for this but I can't seem to get a formula together to include for all the possible special characters. I'm ok with excel but by no means a whizz, if anyone can offer any advice I would be most grateful. Best regards, Gareth |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
I've managed to get the following VBA code from another user and ameded
slightly for my use - this brings up a text box which shows the cell reference and contents in a pop up message box. Is there a way I can replace "'find é in text" with a generic statement which checks for all characters that aren't A-Z, a-z 0-9? The only other alternative would be to type them all out? How can I highlight a cell containing one of these un-required characters with a yellow background (or any colour) instead of it popping up in a message box? Best regards, Gareth Sub FindCellsWithAsterisks() 'find é in text Dim cell As Range, FirstAddress As String, FoundList As String With ActiveSheet.UsedRange 'use tilde to find é Set cell = .Find("~é", LookIn:=xlValues, SearchOrder:=xlByRows, _ LookAt:=xlPart) If Not cell Is Nothing Then FirstAddress = cell.Address '< Bookmark start point Do FoundList = FoundList & "Cell " & cell.Address(0, 0) & _ " =" & vbTab & cell & vbNewLine Set cell = .FindNext(cell) Loop Until cell Is Nothing Or cell.Address = FirstAddress End If End With 'show search results MsgBox FoundList Set cell = Nothing End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Thanks for this one Steffi, for some reason it keeps erroring out :(
'If not.....' line is in red, as is the 1)) and the "Apply" string and the =not. I tried putting the ' infront of the commands but it's not running. I'm not exactly sure what I'm doing wrong, I'm a bit of a novice at VBA. Best regards, Gareth |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Hi Stefi, I've managed to get this working (not so much the conditional
format section though), the VBA had a return value in that I removed and it's all ok. Thanks for the suggestion, it goes some way to sorting this out but it's not exactly what I was after. If anyone else has any other suggestions on this, I've googled all day and there are folks who want to identify these characters (but with varying end reults, most want to auto replace or remove but I don't). I'm sure it's a pretty simple code but it may take a while to get all the characters written in. If this is the case, if someone can start me off I'll happily carry on :) Many thanks, Gareth "Stefi" wrote: Try this UDF: Function standchr(instring) standchr = True For i = 1 To Len(instring) If Not (LCase(Mid(instring, i, 1)) = "a" And LCase(Mid(instring, i, 1)) <= "z") Then standchr = False Exit For End If Next i End Function Apply conditional formatting with formula =NOT( standchr(A1)) -- Regards! Stefi Gareth_Evans (InterCall EMEA) ezt *rta: Hi there, I have a spreadsheet containing first & last names (columns A & B), up to 10,000 rows and some of the names contain 'foreign' characters - such as é. I would like to be able to hightlight all cells that contain a non-standard character (ie: A-Z, a-z, 0-9) which will allow our admin team to eailly filter and correct before the data is loaded into the system. I've been trying to create a macro for this but I can't seem to get a formula together to include for all the possible special characters. I'm ok with excel but by no means a whizz, if anyone can offer any advice I would be most grateful. Best regards, Gareth |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
is not really a "foreign" character.
You say "standard characters that aren't A-Z, a-z, 0-9" What about other standard chars like , . ? " ' ( ) $ % @ * / In a blank sheet in A1 enter =CHAR(ROW()) and copy down to A255 and see the characters produced. Which of those characters would you consider as "foreign"? Perhaps we can narrow it down to a series like char(192) through char(255) Gord Dibben MS Excel MVP Taking you literally.....use this macro but you will get many hits that are not "foreign" characters. On Thu, 4 Feb 2010 06:01:01 -0800, Gareth_Evans (InterCall EMEA) m wrote: Hi there, I have a spreadsheet containing first & last names (columns A & B), up to 10,000 rows and some of the names contain 'foreign' characters - such as . I would like to be able to hightlight all cells that contain a non-standard character (ie: A-Z, a-z, 0-9) which will allow our admin team to eailly filter and correct before the data is loaded into the system. I've been trying to create a macro for this but I can't seem to get a formula together to include for all the possible special characters. I'm ok with excel but by no means a whizz, if anyone can offer any advice I would be most grateful. Best regards, Gareth |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Hi Gord, thank you for your reply and sorry for not defining correctly.
The name strings in the two columns, first & last name, can't contain any characters other than A-Z, a-z, 0-9. No accented characters or symbols. I'd like to identify these entries with some type of marker so they can be manually checked. I'm not looking to auto replace them, just identify them - possibly with a cell highlight but open to suggestions. The system we load these names into doesn't support uni-code (yet) and we get failures on large sheets due to this (we sometimes miss them on the manual check). From your instructions I've used =CHAR(ROW()), it's probably easier to list the one's we'd like to allow as this is a smaller list. Rows: 46 (full stop), 48-57 (0-9), 65-90 (A-Z) and 97-122 (a-z) Not quite a sequential series, but could these be grouped maybe? Thank you for your time, it really is appreciated. Kind regards, Gareth |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Hey Stefi - I got your suggestion working, I don't know what I was doing
wrong but your idea was perfect - spot on! Thank you :) Is there a way I can modify the UDF to allow the following? A space A hyphon (-) Warmest regards, Gareth "Stefi" wrote: Try this UDF: Function standchr(instring) standchr = True For i = 1 To Len(instring) If Not (LCase(Mid(instring, i, 1)) = "a" And LCase(Mid(instring, i, 1)) <= "z") Then standchr = False Exit For End If Next i End Function Apply conditional formatting with formula =NOT( standchr(A1)) -- Regards! Stefi Gareth_Evans (InterCall EMEA) ezt *rta: Hi there, I have a spreadsheet containing first & last names (columns A & B), up to 10,000 rows and some of the names contain 'foreign' characters - such as é. I would like to be able to hightlight all cells that contain a non-standard character (ie: A-Z, a-z, 0-9) which will allow our admin team to eailly filter and correct before the data is loaded into the system. I've been trying to create a macro for this but I can't seem to get a formula together to include for all the possible special characters. I'm ok with excel but by no means a whizz, if anyone can offer any advice I would be most grateful. Best regards, Gareth |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Per your request. 0-9, a-z, A-Z and full stop.
Sub Color_Non_Standard() Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Not Mid(rngR.Value, intI, 1) Like "[A-Z,a-z,0-9,.]" Then rngR.Interior.ColorIndex = 3 End If Next intI Next rngR End Sub Gord On Fri, 5 Feb 2010 01:54:01 -0800, Gareth_Evans (InterCall EMEA) m wrote: Hi Gord, thank you for your reply and sorry for not defining correctly. The name strings in the two columns, first & last name, can't contain any characters other than A-Z, a-z, 0-9. No accented characters or symbols. I'd like to identify these entries with some type of marker so they can be manually checked. I'm not looking to auto replace them, just identify them - possibly with a cell highlight but open to suggestions. The system we load these names into doesn't support uni-code (yet) and we get failures on large sheets due to this (we sometimes miss them on the manual check). From your instructions I've used =CHAR(ROW()), it's probably easier to list the one's we'd like to allow as this is a smaller list. Rows: 46 (full stop), 48-57 (0-9), 65-90 (A-Z) and 97-122 (a-z) Not quite a sequential series, but could these be grouped maybe? Thank you for your time, it really is appreciated. Kind regards, Gareth |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Stefi, Gord and Ron - thank you all so much for your replies. This has
inspired me to learn VBA properly and write a script that will help one of our teams even more. With this sheet I plan to have the cells which contain anything other than A-Z/0-9/space/Hyphen/' show in red. Cells in certain columns which can only have a max amount of characters highlight in blue, an email validation check (for basic format) showing in red also and to turn all cell contents into 'Case Text'. I've even got my workbook to close without giving the user an option to save changes!! I know this is all basic stuff to the pro's but I was so impressed :) Thanks again and, if anyone reads this and can recommend helpful reference material/books for a beginner I'd love to hear from them. Very best regards, Gareth |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
On Tue, 9 Feb 2010 13:45:01 -0800, Gareth_Evans (InterCall EMEA)
m wrote: Stefi, Gord and Ron - thank you all so much for your replies. This has inspired me to learn VBA properly and write a script that will help one of our teams even more. With this sheet I plan to have the cells which contain anything other than A-Z/0-9/space/Hyphen/' show in red. Cells in certain columns which can only have a max amount of characters highlight in blue, an email validation check (for basic format) showing in red also and to turn all cell contents into 'Case Text'. I've even got my workbook to close without giving the user an option to save changes!! I know this is all basic stuff to the pro's but I was so impressed :) Thanks again and, if anyone reads this and can recommend helpful reference material/books for a beginner I'd love to hear from them. Very best regards, Gareth Glad to help. Thanks for the feedback. Hopefully, one of the others will have some references for you. --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
Thanks for the feedback.
As far as books go.................. See Debra Dalgleish's site for a list of books. http://www.contextures.on.ca/xlbooks.html Under Functions, anything by John Walkenbach Under VBA, any of the first three listed and any other by John Walkenbach. Also Reed Jacobsen books are generally pretty good in my experience. Gord Dibben MS Excel MVP On Tue, 9 Feb 2010 13:45:01 -0800, Gareth_Evans (InterCall EMEA) m wrote: Stefi, Gord and Ron - thank you all so much for your replies. This has inspired me to learn VBA properly and write a script that will help one of our teams even more. With this sheet I plan to have the cells which contain anything other than A-Z/0-9/space/Hyphen/' show in red. Cells in certain columns which can only have a max amount of characters highlight in blue, an email validation check (for basic format) showing in red also and to turn all cell contents into 'Case Text'. I've even got my workbook to close without giving the user an option to save changes!! I know this is all basic stuff to the pro's but I was so impressed :) Thanks again and, if anyone reads this and can recommend helpful reference material/books for a beginner I'd love to hear from them. Very best regards, Gareth |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hightlight cells that contain special characters
You are welcome! Thanks for the feedback!
I'd suggest to make use of macro recording, I found it a very useful mean - together with reading Help - when learning VBA. -- Regards! Stefi Gareth_Evans (InterCall EMEA) ezt *rta: Stefi, Gord and Ron - thank you all so much for your replies. This has inspired me to learn VBA properly and write a script that will help one of our teams even more. With this sheet I plan to have the cells which contain anything other than A-Z/0-9/space/Hyphen/' show in red. Cells in certain columns which can only have a max amount of characters highlight in blue, an email validation check (for basic format) showing in red also and to turn all cell contents into 'Case Text'. I've even got my workbook to close without giving the user an option to save changes!! I know this is all basic stuff to the pro's but I was so impressed :) Thanks again and, if anyone reads this and can recommend helpful reference material/books for a beginner I'd love to hear from them. Very best regards, Gareth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format cells for special characters | New Users to Excel | |||
Format cells for special characters | New Users to Excel | |||
Automatically highlight cells with special characters | Excel Discussion (Misc queries) | |||
special characters (little box) | Excel Discussion (Misc queries) | |||
REMOVE SPECIAL CHARACTERS FROM TEXT CELLS | Excel Worksheet Functions |