Find Special Characters

[mark.wolven]

Currently, the data set that I am working with isn't all that large.
But, I expect the data sets to grow.

That said, what I would like to be able to do, would be to execute a
function that searches for special characters. It' doesn't have to
replace them, all I'd need is a TRUE FALSE answer to the question, is
there a special character in this cell. Then I could use that formula
to flag the cells that need to be cleaned up before they are imported.

Also, is there a way to identify a low value character in a specified
position in a cell. I am also trying to identify cells that begin with
a space, paren, underscore, etc.

Any thoughts on one or both of these would be grrrreatly appreciated.

 

[Pete_UK]

Second one first - try a formula like this:

=IF(OR(LEFT(A1,1)=" ",LEFT"A1,1)="(",LEFT(A1,1)="_"),"low_value","ok")

assuming data is in A1 - copy down as required.

Now the first part - what do you consider to be a "special character"?
You can use FIND or SEARCH to look for a particular character, eg.:

=IF(ISNUMBER(FIND("_",A1)),"special","ok")

You can also use Edit | Replace (CTRL-H) after highlighting the column
to repeatedly find a character and replace it with nothing. Or you can
do this by formula using the SUBSTITUTE function.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Currently, the data set that I am working with isn't all that large.
But, I expect the data sets to grow.

That said, what I would like to be able to do, would be to execute a
function that searches for special characters. It' doesn't have to
replace them, all I'd need is a TRUE FALSE answer to the question, is
there a special character in this cell. Then I could use that formula
to flag the cells that need to be cleaned up before they are imported.

Also, is there a way to identify a low value character in a specified
position in a cell. I am also trying to identify cells that begin with
a space, paren, underscore, etc.

Any thoughts on one or both of these would be grrrreatly appreciated.

It's pretty simple so long as you define what you mean by "special character".
You could also use the routine to remove them.


--ron