Results 1 to 12 of 12

Thread: MS Excel CONCATENATE formula help

  1. #1
    My Heaven's Avatar
    My Heaven is offline Advance Member
    Last Online
    22nd July 2021 @ 12:31 PM
    Join Date
    26 Nov 2010
    Location
    Kashmore
    Gender
    Male
    Posts
    2,805
    Threads
    306
    Credits
    7,621
    Thanked
    137

    Default MS Excel CONCATENATE formula help

    Assalam Alaikm dosto umeed karta hun sab kheriat se honge.
    MS Excel me ek problem arahi hai
    mere pas kuch data hai jo ek hi column me 1042361 cells me hai . me us ko ek Cell me karna chahta hun. jis k lye maine FORMULA =TRANSPOSE(CONCATENATE(A1:A500)) bhi apply kya but ye 500 cells tak hi karta hai. 1042361 cells ko karne me mjhe sara din lag jaega . plz koi tareeka bataen kam se kam 5000 i mean =TRANSPOSE(CONCATENATE(A1:A5000)) tak kaam kare. jab 5000 ka forumula lgata hun to Formula too long ka error ajata hai. Umeed hai me apna masla ap experts ko samjha paya hoga. Iam waiting for experts reply.

  2. #2
    syar is offline Advance Member
    Last Online
    14th March 2024 @ 07:49 PM
    Join Date
    31 Jul 2009
    Location
    Nowshera(KP)
    Age
    47
    Gender
    Male
    Posts
    882
    Threads
    9
    Credits
    548
    Thanked
    340

    Default

    Quote EmranHashmi said: View Post
    Assalam Alaikm dosto umeed karta hun sab kheriat se honge.
    MS Excel me ek problem arahi hai
    mere pas kuch data hai jo ek hi column me 1042361 cells me hai . me us ko ek Cell me karna chahta hun. jis k lye maine FORMULA =TRANSPOSE(CONCATENATE(A1:A500)) bhi apply kya but ye 500 cells tak hi karta hai. 1042361 cells ko karne me mjhe sara din lag jaega . plz koi tareeka bataen kam se kam 5000 i mean =TRANSPOSE(CONCATENATE(A1:A5000)) tak kaam kare. jab 5000 ka forumula lgata hun to Formula too long ka error ajata hai. Umeed hai me apna masla ap experts ko samjha paya hoga. Iam waiting for experts reply.
    Agar m ghalti naee kr raha tu aap formula ulta laga rahe hain may be typo.
    Code:
    =concatenate(transpose(range))
    Excel k cells ki apni limit hote ha. jis ki waja sa ya masle aate hain. excel k cel m(2010,2013,2016 excel) m aik cell m aap 10000 charachter lekh sakte hain. aur normally ya 256 charachter ki hote ha. Is waja sa na he aap itne lambi range p concatenate ya transpose ka function naee use kr skate. Aap formula choti range p lagain tu thk rahe ga. Excel 4000 rows tk formula ko calculate kar le ga. lekan jab result cell m show krna ho ga tu ya phir error de ga aap ko q k cell m itne character naee atte.
    agar aap bata sakain k aap krna kya chah rahe hain jis m itne zayda cell involve hain tu shayd koi dosra method ho jis ki waja sa aap ka masla hal ho jay. other wise aisa mumkin naee.
    N.B: ya masla concatenate aur transpose function ko use krne m aata ha. sum, divide etc p kitne bhe lambi range ho koi masla naee ho ga.

    regards,

  3. #3
    My Heaven's Avatar
    My Heaven is offline Advance Member
    Last Online
    22nd July 2021 @ 12:31 PM
    Join Date
    26 Nov 2010
    Location
    Kashmore
    Gender
    Male
    Posts
    2,805
    Threads
    306
    Credits
    7,621
    Thanked
    137

    Default

    Quote syar said: View Post
    Agar m ghalti naee kr raha tu aap formula ulta laga rahe hain may be typo.
    Code:
    =concatenate(transpose(range))
    Excel k cells ki apni limit hote ha. jis ki waja sa ya masle aate hain. excel k cel m(2010,2013,2016 excel) m aik cell m aap 10000 charachter lekh sakte hain. aur normally ya 256 charachter ki hote ha. Is waja sa na he aap itne lambi range p concatenate ya transpose ka function naee use kr skate. Aap formula choti range p lagain tu thk rahe ga. Excel 4000 rows tk formula ko calculate kar le ga. lekan jab result cell m show krna ho ga tu ya phir error de ga aap ko q k cell m itne character naee atte.
    agar aap bata sakain k aap krna kya chah rahe hain jis m itne zayda cell involve hain tu shayd koi dosra method ho jis ki waja sa aap ka masla hal ho jay. other wise aisa mumkin naee.
    N.B: ya masla concatenate aur transpose function ko use krne m aata ha. sum, divide etc p kitne bhe lambi range ho koi masla naee ho ga.

    regards,
    sir first of all Thankx allot ap ne reply kya. Actually ek dost se maine ek book maangi thi us ne wo book MS Word k bajae EXCEL me type kardi. us me kuch notes bhi hai. jo k Cell A1 se le kar A1047200 tak ek hi column me devide ho gai. uski mistake se ye hua. lekin ab un notes ko ek hi cell me combined kar k Notepad me Save karna chahta hun. lekin jab 1047200 cells ko automatic notpad me save karta hun to phir bhi wohi har word new line par ata hai. is lye chahta hun k notes ko ek hi cell me combined karun phir unko set kar k Notpad me save karun ta k read karne me easy rahy.
    Regards,

  4. #4
    syar is offline Advance Member
    Last Online
    14th March 2024 @ 07:49 PM
    Join Date
    31 Jul 2009
    Location
    Nowshera(KP)
    Age
    47
    Gender
    Male
    Posts
    882
    Threads
    9
    Credits
    548
    Thanked
    340

    Default

    Quote EmranHashmi said: View Post


    sir first of all Thankx allot ap ne reply kya. Actually ek dost se maine ek book maangi thi us ne wo book MS Word k bajae EXCEL me type kardi. us me kuch notes bhi hai. jo k Cell A1 se le kar A1047200 tak ek hi column me devide ho gai. uski mistake se ye hua. lekin ab un notes ko ek hi cell me combined kar k Notepad me Save karna chahta hun. lekin jab 1047200 cells ko automatic notpad me save karta hun to phir bhi wohi har word new line par ata hai. is lye chahta hun k notes ko ek hi cell me combined karun phir unko set kar k Notpad me save karun ta k read karne me easy rahy.
    Regards,
    Aap excel sa direct word m copy q naee krte? kya aap n copy paste ka method try kya?
    excel sa copy karain aur word m Paste special k through as Text copy karain. may be aap ka masla hal ho jai ga. aur jo line break ho use VBA k through clean kar lain.

    regards,

  5. #5
    My Heaven's Avatar
    My Heaven is offline Advance Member
    Last Online
    22nd July 2021 @ 12:31 PM
    Join Date
    26 Nov 2010
    Location
    Kashmore
    Gender
    Male
    Posts
    2,805
    Threads
    306
    Credits
    7,621
    Thanked
    137

    Default

    Quote syar said: View Post
    Aap excel sa direct word m copy q naee krte? kya aap n copy paste ka method try kya?
    excel sa copy karain aur word m Paste special k through as Text copy karain. may be aap ka masla hal ho jai ga. aur jo line break ho use VBA k through clean kar lain.

    regards,
    g bhai ye kar chuka hun but word me bhi to wohi new line par ata hai . mjhe paragraph type book banani hai. ek ek word se to bohot time lag jana hai.

  6. #6
    jk khan is offline Senior Member+
    Last Online
    2nd March 2024 @ 08:11 PM
    Join Date
    11 Nov 2015
    Age
    31
    Gender
    Male
    Posts
    108
    Threads
    7
    Credits
    924
    Thanked
    0

    Default

    bhai me apko btata hon is ka solution

  7. #7
    jk khan is offline Senior Member+
    Last Online
    2nd March 2024 @ 08:11 PM
    Join Date
    11 Nov 2015
    Age
    31
    Gender
    Male
    Posts
    108
    Threads
    7
    Credits
    924
    Thanked
    0

    Default

    bhai ap istarah krain keh Excel ke tmam text ko select kr lain phr word me jain wahan object ke madad sy excel ke file ko insert krain phr jb word me excel ke file a jay phr to usko convert table to text kr dain .
    phr woh table text me convert ho jay ge or paragraph me bhe .Thanks

  8. #8
    My Heaven's Avatar
    My Heaven is offline Advance Member
    Last Online
    22nd July 2021 @ 12:31 PM
    Join Date
    26 Nov 2010
    Location
    Kashmore
    Gender
    Male
    Posts
    2,805
    Threads
    306
    Credits
    7,621
    Thanked
    137

    Default

    Quote jk khan said: View Post
    bhai ap istarah krain keh Excel ke tmam text ko select kr lain phr word me jain wahan object ke madad sy excel ke file ko insert krain phr jb word me excel ke file a jay phr to usko convert table to text kr dain .
    phr woh table text me convert ho jay ge or paragraph me bhe .Thanks
    g bhai bilkul object k through ad kar lia . table to text ka option nahi mil raha

  9. #9
    jk khan is offline Senior Member+
    Last Online
    2nd March 2024 @ 08:11 PM
    Join Date
    11 Nov 2015
    Age
    31
    Gender
    Male
    Posts
    108
    Threads
    7
    Credits
    924
    Thanked
    0

    Default

    MS ofice konsa version use kr rahay ho ?

  10. #10
    My Heaven's Avatar
    My Heaven is offline Advance Member
    Last Online
    22nd July 2021 @ 12:31 PM
    Join Date
    26 Nov 2010
    Location
    Kashmore
    Gender
    Male
    Posts
    2,805
    Threads
    306
    Credits
    7,621
    Thanked
    137

    Default

    Quote jk khan said: View Post
    MS ofice konsa version use kr rahay ho ?
    2007

  11. #11
    jk khan is offline Senior Member+
    Last Online
    2nd March 2024 @ 08:11 PM
    Join Date
    11 Nov 2015
    Age
    31
    Gender
    Male
    Posts
    108
    Threads
    7
    Credits
    924
    Thanked
    0

    Default

    yar 2010 use kro us me hoga easily convert table to text

  12. #12
    My Heaven's Avatar
    My Heaven is offline Advance Member
    Last Online
    22nd July 2021 @ 12:31 PM
    Join Date
    26 Nov 2010
    Location
    Kashmore
    Gender
    Male
    Posts
    2,805
    Threads
    306
    Credits
    7,621
    Thanked
    137

    Default

    Quote jk khan said: View Post
    yar 2010 use kro us me hoga easily convert table to text
    thk hai me abhi install kar leta hun .kindly thora guide krden option kis jaga hoga.

Similar Threads

  1. Excel tip (Hidde formula in formula bar in excel)
    By Honour12 in forum Urdu Tutorials & Designing
    Replies: 14
    Last Post: 31st May 2016, 10:06 AM
  2. Excel Formula
    By Hassankhan88 in forum Ask an Expert
    Replies: 2
    Last Post: 22nd October 2015, 08:09 PM
  3. Solved Hid Formula in excel
    By Shireen Shari in forum Solved Problems (IT)
    Replies: 9
    Last Post: 13th December 2012, 09:36 PM
  4. Solved Help a excel formula?
    By Ameen Asim in forum Solved Problems (IT)
    Replies: 8
    Last Post: 25th November 2011, 09:20 PM
  5. help about excel formula....?
    By ihtesham_ulfat in forum Ask an Expert
    Replies: 1
    Last Post: 24th March 2009, 10:24 AM

Bookmarks

Posting Permissions

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