Need help with a complicated formula

[pe]

I need a formula that if the value of the cell is a hyphen then it looks at the letter in parens in column A of the matching row and deletes that letter from the column one right of where it found the hyphen.

For instance, if a hyphen appears in F15. In A15 there is a (N) at the end of the text in that cell. The N must then be deleted from G15. G15 has ABCNP in it so now it needs to be ABCP.

Well, maybe a formula will not work and I need a macro to search the cells from A150 to see if there is a hyphen in them and then delete the proper letter...

Hope someone can give me guidance. Thanks

 

[JustImagine2525]

Hello - Check this out. You did not say if the letter in parens was always at the end. This solution won't care. Also, you did not say if the hyphen column ONLY contains a hyphen, this solution assumes it does. Feel free to contact me at mhappe@ipsconsult.com

Find Parens
FIND("(",A15) h15
Identify Letter in parens
MID(A15,H15+1,1)
get location of letter in string
FIND(I15,G15)
remove letter in string
REPLACE(G15,J15,1,"&quot
Result
IF(CODE(E15)=45,REPLACE(G15,FIND(MID(A15,FIND("(",A15)+1,1),G15),1,"&quot,"&quot

 

[pe]

wowie! thank you very much

 

[JustImagine2525]

PE,

How is it going? Did you get this to work for your application? I forgot to tell you the code(e15=45 is for the hyphen search. ANSI code for hyphen is 45. Let me know if I can be of further assistance.

Mark