Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Thanked 1 Time in 1 Post

    Complicated formula or macro (excel xp)

    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 A1:P50 to see if there is a hyphen in them and then delete the proper letter...

    Hope someone can give me guidance. Thanks
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Ross-on-Wye, Herefordshire, United Kingdom
    Thanked 0 Times in 0 Posts

    Re: Complicated formula or macro (excel xp)

    Hi There

    =IF(F15="-",IF(RIGHT(A15,1)=")",SUBSTITUTE(G15,MID(A15,FIND( ")",A15)-1,1),""),15),G15)

    <font color=red>Whoops... Typo... should read:

    =IF(F15="-",IF(RIGHT(A15,1)=")",SUBSTITUTE(G15,MID(A15,FIND( ")",A15)-1,1),""),G15),G15)
    <font color=black>
    In Cell G16, given your example, will work. i.e. In the cell NEXT (could be anywhere though) to where you currently have the text. There may be a neater macro solution, I'm not experienced enough to do that though. FIND is case sensitive, use SEARCH instead if you need to ignore case.

    Example win Excel 2K attached.

    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts