I have a column of numbers that were scanned in and OCR often makes mistakes
inserting a "S" instead of an "8" for example or an "O" instead of an "0".
Any ideas on how I can find any cells that contain A-Z in a cell of numbers?
I tried just sorting and picking up the mistakes at the bottom, however, that
does not always work. If the list of telephone numbers 000-000-0000 contains
a letter 603-5S5-0000 this does not sort to the bottom..

Thanks for the help!

If the format your dealing with is all like that:
000-000-0000

Perhaps this will do the trick:

=IF(ISNUMBER(VALUE(SUBSTITUTE(A2,"-",""))),"","Letter present")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jon Hebert" wrote:

I have a column of numbers that were scanned in and OCR often makes mistakes
inserting a "S" instead of an "8" for example or an "O" instead of an "0".
Any ideas on how I can find any cells that contain A-Z in a cell of numbers?
I tried just sorting and picking up the mistakes at the bottom, however, that
does not always work. If the list of telephone numbers 000-000-0000 contains
a letter 603-5S5-0000 this does not sort to the bottom..

Thanks for the help!

I placed 000-00A-0000 in A2 and typed your formula in B2
I got "False" as a result.

I then ran your formula on 000-000-0000 and I got Letter present..

What do you think?

Thank you!

"Luke M" wrote:

If the format your dealing with is all like that:
000-000-0000

Perhaps this will do the trick:

=IF(ISNUMBER(VALUE(SUBSTITUTE(A2,"-",""))),"","Letter present")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jon Hebert" wrote:

I have a column of numbers that were scanned in and OCR often makes mistakes
inserting a "S" instead of an "8" for example or an "O" instead of an "0".
Any ideas on how I can find any cells that contain A-Z in a cell of numbers?
I tried just sorting and picking up the mistakes at the bottom, however, that
does not always work. If the list of telephone numbers 000-000-0000 contains
a letter 603-5S5-0000 this does not sort to the bottom..

Thanks for the help!

Jon Hebert,

The formula works for me - see the attached workbook "Find cell
containing a letter - Jon Hebert - SDG.xls".


Have a great day,
Stan


+-------------------------------------------------------------------+
|Filename: Find cell containing a letter - Jon Hebert - SDG.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=190|
+-------------------------------------------------------------------+

--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?userid=503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119215

Did you copy formula exaclty? When I copy your examples into A2 on my sheet,
it works. Perhaps Stan's workbook can shed some light?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jon Hebert" wrote:

I placed 000-00A-0000 in A2 and typed your formula in B2
I got "False" as a result.

I then ran your formula on 000-000-0000 and I got Letter present..

What do you think?

Thank you!

"Luke M" wrote:

If the format your dealing with is all like that:
000-000-0000

Perhaps this will do the trick:

=IF(ISNUMBER(VALUE(SUBSTITUTE(A2,"-",""))),"","Letter present")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jon Hebert" wrote:

I have a column of numbers that were scanned in and OCR often makes mistakes
inserting a "S" instead of an "8" for example or an "O" instead of an "0".
Any ideas on how I can find any cells that contain A-Z in a cell of numbers?
I tried just sorting and picking up the mistakes at the bottom, however, that
does not always work. If the list of telephone numbers 000-000-0000 contains
a letter 603-5S5-0000 this does not sort to the bottom..

Thanks for the help!

Worked like a Charm!

Thank you for your time!

Jon

"stanleydgromjr" wrote:


Jon Hebert,

The formula works for me - see the attached workbook "Find cell
containing a letter - Jon Hebert - SDG.xls".


Have a great day,
Stan


+-------------------------------------------------------------------+
|Filename: Find cell containing a letter - Jon Hebert - SDG.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=190|
+-------------------------------------------------------------------+

--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?userid=503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119215

Thank you for taking the time to check this. Luke M's sheet showed me my
mistake!

I appreciate your time!
Jon

"stanleydgromjr" wrote:


Jon Hebert,

The formula works for me - see the attached workbook "Find cell
containing a letter - Jon Hebert - SDG.xls".


Have a great day,
Stan


+-------------------------------------------------------------------+
|Filename: Find cell containing a letter - Jon Hebert - SDG.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=190|
+-------------------------------------------------------------------+

--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?userid=503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119215