# replace values in a cell with nothing blank

Hi Experts

Need a formula or macro to replace all cell values that start with letter o or e..could be caps and have 6 numbers after the letter with a -...
So.
1. O123356 to -
2. e787657 to -

Data range c2:cz20000
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft Excel ExpertCommented:
put this in DA and drag left and down

=SUBSTITUTE(C2,LEFT(C2,1),"e")
Commented:
Hi,

pls try

``````Sub macro()
For Each c In Range("C2:CZ20000")
If UCase(c) Like "O######" Or UCase(c) Like "E######" Then
c = "'-"
End If
Next
End Sub
``````
EDIT Corrected code

Regards
Microsoft Excel ExpertCommented:
also a better one. use below, put this in DA1 and drag left and down.

since you asked for formula solution, here is the formula.

=IF(AND(LEN(C2)=7,(SEARCH("o",LEFT(C2,1),1))=1),SUBSTITUTE(C2,LEFT(C2,1),"e"),C2)
JuniorAuthor Commented:
Thanks for the feedback
Microsoft Excel ExpertCommented:
i misunderstood your question, here is the correct formula put this in DA1 and drag left and down

=IF(AND(LEN(C2)=7,OR(ISNUMBER(SEARCH("o",LEFT(C2,1),1)),ISNUMBER(SEARCH("e",LEFT(C2,1),1)))),SUBSTITUTE(C2,LEFT(C2,1),"-"),C2)
JuniorAuthor Commented:
Professor Jim jam. ..the formula partly works it still leaves the numbers numerical value...need to remove that too...

So o123456 equal - not 123456
Microsoft Excel ExpertCommented:
then use this

=IF(AND(LEN(C2)=7,OR(ISNUMBER(SEARCH("o",LEFT(C2,1),1)),ISNUMBER(SEARCH("e",LEFT(C2,1),1)))),SUBSTITUTE(C2,C2,"-"),C2)

Experts Exchange Solution brought to you by