1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in Excel formula to address FullSalutation

Discussion in 'Ask an Excel Question' started by Madhuri_G, Oct 5, 2018.

  1. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Hi Chandoo/Brilliant Guys out there..

    I just want to know answer for the below scenario... I tried this formula but it's not working. Can you please help me guys..

    I have data in 8SEGMENTS with contacts FULLSalutation, for contacts under 18 they should be addressed as Lastname + Familyname.
    For records with if name has one initial, or initials, or dots(anomalies) it should change to 'Supporter'

    I'm getting error for one initial, or initials, or dots(anomalies)

    Can you please rectify this??
    =IF(AND(AK5<18,AK5<>""),CONCATENATE(H5," ","Family",""),IF(OR(CD5="SEGMENT_1",CD5="SEGMENT_2",CD5="SEGMENT_3",CD5="SEGMENT_4",CD5="SEGMENT_5",CD5="SEGMENT_6",CD5="SEGMENT_7",CD5="SEGMENT_8"),
    IF(BJ5,"Supporter",
    IF(OR(I5="",I5=".",I5="Unknown",LEN(TRIM(I5))=1),
    E5&" "&H5,
    I5)
    ),
    ))
  2. AlanSidman

    AlanSidman Active Member

    Messages:
    413
    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.
  3. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Hi Alan,
    Thanks so much for quick reply,, I have attached sample file, Before and After(which I manually did ) It should be the ACTUAL OUTPUT.. Hope you got it,, All there scenarios should come in one place..Book1 is BEFORE... Book2 is AFTER(output)should be...

    Please help

    Attached Files:

  4. AlanSidman

    AlanSidman Active Member

    Messages:
    413
    Add these to your OR statement
  5. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Hi Alan,

    You missed the formula.. can you please send again.. what needs to be added to OR?
  6. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    If you could see i have already added OR statement..
    =IF(AND(E3<18,AK6<>""),CONCATENATE(D3," ","Family",""),IF(OR(G3="SEGMENT_1",G3="SEGMENT_2",G3="SEGMENT_3",G3="SEGMENT_4",G3="SEGMENT_5",G3="SEGMENT_6",G3="SEGMENT_7",G3="SEGMENT_8"),
    IF(F3,"Supporter",
    IF(OR(C3="",C3=".",C3="Unknown",LEN(TRIM(C3))=1),
    B3&" "&D3,
    C3)
    ),
    ))
  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037

    Changed your green part from :

    =IF(OR(C3="",C3=".",C3="Unknown",LEN(TRIM(C3))=1),B3&" "&D3,C3)

    Into this :

    =IF(OR(C3="",ISNUMBER(FIND(".",C3)),C3="Unknown",LEN(TRIM(C3))=1),B3&" "&D3,C3)

    Regards
    Bosco
  8. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Thankyou Bosco!! It worked for my Sample data.. I will try in my original Data on Monday and will let you know.. thanks a ton!:)
  9. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Hi ,

    I'm still getting two initials from the formula, these needs to be get rid off too and replace with 'Supporter'. Can anyone show me some light on this??

    A & C
    A & P
    A & L
    A & T
    A & Kitsa
    A & S
    A L
    A & C
    A & M
    A J
    A & C
    A & G
    A & C
    A & S
    A & C
    A & M
    A & L
    A & G
    A & M

    These should be masked with 'Supporter'

    =IF(AND(AK5<18,AK5<>""),CONCATENATE(H5," ","Family",""),IF(OR(CD5="SEGMENT_1",CD5="SEGMENT_2",CD5="SEGMENT_3",CD5="SEGMENT_4",CD5="SEGMENT_5",CD5="SEGMENT_6",CD5="SEGMENT_7",CD5="SEGMENT_8"),
    IF(BJ5,"Supporter",
    IF(OR(I5="",ISNUMBER(FIND(".",I5)),I5="Unknown",LEN(TRIM(I5))=1),
    E5&" "&H5,
    I5)
    ),
    ))
  10. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Hi,

    I have encountered a pattern in my excel and I need to change this to 'Supporter'.

    The pattern is alphabet and space...
    A L - Alphabet and space
    B L
    C B
    D A

    which function should i use???

    I thought of using FIND function, but there are 26 alphabets to find out...
  11. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Please check the AFTER & Before formula in Excel, I need FULLSALUTATION formula, it should return "Supporter" if column F is FALSE and has a SEGMENT accociated to it.

    For dots(anamolies), one initial, two initials - it must return "Supporter". Any quick help is appreciated guys... Pls

    Attached Files:

  12. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
  13. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Please reply us as in :

    1] Remove formula in "After" sheet H2:H21

    2] Manual write down what is your expected result in "After" sheet H2:H21

    3] What are the conditions in order to obtain your expected result.

    4] re-upload of your file in respect the above questions

    Regards
    Bosco
  14. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Hi @bosco_yip

    1] I have added Before & After sheet to be in column "H"

    2] I have written it and its in Red colour (After sheet) column (26 to 33)

    3] For First name (A.M, E.N. , A & N, B & P, B.K ) for all these scenarios output (column H should be "Supporter" ) and for First name Bo, An - it should be as it is..

    4] I have attached the file can you please look it

    Attached Files:

  15. Haz

    Haz Active Member

    Messages:
    114
    Formula in F2:
    =IF(OR(C2="",C2="unknown",LEN(C2)=1,AND(SUMPRODUCT(IFERROR(FIND({" ","&","."},C2),0))>0,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ",),"&",),".",))<3)),TRUE,FALSE)
    Thomas Kuriakose likes this.
  16. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    Thanks @Haz Will try this and let you know,, I think for this scenario if First name is "George." it displayed as Supporter - it should be "George." only because its length is <3
  17. Madhuri_G

    Madhuri_G New Member

    Messages:
    21
    @Haz Excellent! Its working fine!! thankuuu
  18. Haz

    Haz Active Member

    Messages:
    114
    You may want AS (in C20) to show as Supporter too, in that case change the formula to this

    =IF(OR(C20="",C20="unknown",LEN(C20)=1,AND(OR(SUMPRODUCT(IFERROR(FIND({" ","&","."},C20),0))>0,EXACT(UPPER(C20),C20)),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C20," ",),"&",),".",))<3)),TRUE,FALSE)

Share This Page