Results 1 to 3 of 3

Thread: Reverse Name with upper case with comma

  1. #1

    Post Reverse Name with upper case with comma



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi,

    I need to Reverse Name with some condition ,

    need to break the name in half part and reverse the name with comma ,and need to take dot as one word like EG- shrinivas m.j. are 3 words .

    1 to 3 words reverse last word


    > 4 to 5 words reverse last 2 words

    > 6 to 7 words reverse last 3 words

    Eg shown below

    INPUT NAME OUTPUT NAME
    N. Yulia Worbe WORBE,N. YULIA ( Need to take space after Dot N. )
    Gerardin N. T. T,GERARDIN N.
    N. T. Andreas ANDREAS,N. T.
    n.t. Romain ROMAIN,N.T. ( No Space After Dot N.T. )
    Marie Chupin P.L.N. P. L.N. P,MARIE CHUPIN P.
    P. L. O. P. Leon Tremblay P. LEON TREMBLAY,P. L. O.
    Marie Vidailhet N. N,MARIE VIDAILHET
    Olivier Colliot K. T. P. T. P,OLIVIER COLLIOT K.
    S.S. Arba ARBA,S.S.

    PLEASE FIND THE OLD FORMULA USED,BUT TAKING DOT IN REVERSE AND GIVING SPACE AFTER DOT.

    =UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(N68)," ",REPT(" ",99)),(1+(LEN(N68)-LEN(SUBSTITUTE(N68," ",""))>2)+(LEN(N68)-LEN(SUBSTITUTE(N68," ",""))>4))*99))&","&N68,LEN(N68)))

  2. #2
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    33
    Articles
    0
    add substitute formula to this formula
    =SUBSTITUTE(UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(C3)," ",REPT(" ",99)),(1+(LEN(N68)-LEN(SUBSTITUTE(C3," ",""))>2)+(LEN(C3)-LEN(SUBSTITUTE(C3," ",""))>4))*99))&","&C3,LEN(C3)))," ","")
    this formula will delete the space bar

  3. #3
    hi currently using this formula ,

    i need , if the name as space after dot . output need to take space after dot or if no space after dot need to take with out space after dot in output.

    EG,

    INPUT OUTPUT
    N. Yulia Worbe WORBE,N. YULIA ( NED TO TAKE AS INPUT SPACE AFTER DOT )

    n.t. Romain ROMAIN,N.T. ( No Space After Dot IN INPUT )

    NEED TO GET same output for one formula NEED TO EDIT .

    =UPPER(MID(TRIM(SUBSTITUTE(H14,".",". "))&","&TRIM(SUBSTITUTE(H14,".",". ")),FIND("$",SUBSTITUTE(TRIM(SUBSTITUTE(H14,".",". "))," ","$",LOOKUP(LEN(TRIM(SUBSTITUTE(H14,".",". ")))-LEN(SUBSTITUTE(TRIM(H14)," ",""))+1,{0,4,6},(LEN(TRIM(SUBSTITUTE(H14,".",". ")))-LEN(SUBSTITUTE(SUBSTITUTE(H14,".",". ")," ","")))-{0,1,2})))+1,LEN(TRIM(SUBSTITUTE(H14,".",". ")))))

Tags for this Thread

Posting Permissions

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