Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Need formula or script to devide text

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need formula or script to devide text

    Hi All,

    I have a set of First Names and Last names in my application in below format.


    When i am using text to columns, i am getting output as


    But, I need output as below :




    Can someone help me to get the required output? or else please let me know at least, is it possible or not..!

    Thanks in advance..!
    Last edited by Fluff; Mar 1st, 2018 at 03:22 PM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need formula or script to devide text

    Hi & welcome to MrExcel
    How about
    Code:
    Sub SplitTranspose()
    
       Dim Hdr As Variant
       Dim Ary As Variant
       Dim i As Long
       
       Hdr = Range("A1:A2")
       Ary = Range("B1:B2")
       Range("A1:B2").Clear
       Range("A1:B1").Value = Hdr
       For i = 1 To 2
          Cells(2, i).Resize(UBound(Split(Ary(i, 1), "/")) + 1).Value = _
             Application.Transpose(Split(Ary(i, 1), "/"))
       Next i
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular
    Join Date
    Sep 2017
    Posts
    118
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need formula or script to devide text

    Or without VBA.

    You can copy the selection then pastespecial transpose.

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need formula or script to devide text

    Hi Thanks for the reply,

    if you don't mind could you please tell me how to use this code?

    i have added code when i tried to run macro using ALT- F8 it showing some type mismatch error. do i need to change cell numbers in above code?

    debug option showing this as error : Cells(2, i).Resize(UBound(Split(Ary(i, 1), "/")) + 1).Value = _
    Application.Transpose(Split(Ary(i, 1), "/"))

    Can you please guide me to run the above code.


    Quote Originally Posted by Fluff View Post
    Hi & welcome to MrExcel
    How about
    Code:
    Sub SplitTranspose()
    
       Dim Hdr As Variant
       Dim Ary As Variant
       Dim i As Long
       
       Hdr = Range("A1:A2")
       Ary = Range("B1:B2")
       Range("A1:B2").Clear
       Range("A1:B1").Value = Hdr
       For i = 1 To 2
          Cells(2, i).Resize(UBound(Split(Ary(i, 1), "/")) + 1).Value = _
             Application.Transpose(Split(Ary(i, 1), "/"))
       Next i
    End Sub

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need formula or script to devide text

    How long are the strings in B1 & B2?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need formula or script to devide text

    Another option, try this instead
    Code:
    Sub SplitTranspose()
    
       Dim Hdr As Variant
       Dim Ary As Variant
       Dim i As Long, j As Long
       
       Hdr = Range("A1:A2")
       Ary = Range("B1:B2")
       Range("A1:B2").Clear
       Range("A1:B1").Value = Hdr
       For i = 1 To 2
          For j = 0 To UBound(Split(Ary(i, 1), "/"))
             Cells(j + 2, i).Value = Split(Ary(i, 1), "/")(j)
          Next j
       Next i
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need formula or script to devide text

    Hi @Fluff

    Don't know how could i say thanks to you.

    Its really great. its working perfectly for me. However, can you please clarify my doubts on this?

    • Will this work for long names also?
    • I mean do we have any limitation that name should be have below n number of words?


    I tried for below names, It works perfectly. (It has 11 names)




    The code perfectly working when i paste First Names in B1 Cell and Last Names in B2 cell. Those are at top left corner in document.

    If i want to make document look beautiful, I need to paste these names in middle of the document.
    Example: 10th and 11th rows or 12th and 13th rows. It maybe C10,C11 or D10 D11

    Then what should we need to modify in below code? I tried with replacing B1 with D9 and B2 with D10. I ran the code with keeping names in D9 D10 but i am getting "#N/A" along with names in output. And also output names are starting from A2 B2. Can we modify code for output start from some specifc cells?

    This is the output i got after replacing B1B2 with D9D10


    and also i would like to know possibilities for concatinating these two output names through this code. Below is the expected output in this case

    Expected Output :



    I am very happy to be here @Fluff and once again thank you so much for your help !


    Quote Originally Posted by Fluff View Post
    Another option, try this instead
    Code:
    Sub SplitTranspose()
    
       Dim Hdr As Variant
       Dim Ary As Variant
       Dim i As Long, j As Long
       
       Hdr = Range("A1:A2")
       Ary = Range("B1:B2")
       Range("A1:B2").Clear
       Range("A1:B1").Value = Hdr
       For i = 1 To 2
          For j = 0 To UBound(Split(Ary(i, 1), "/"))
             Cells(j + 2, i).Value = Split(Ary(i, 1), "/")(j)
          Next j
       Next i
    End Sub
    Last edited by Fluff; Mar 1st, 2018 at 03:23 PM.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need formula or script to devide text

    Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
    Please be patient and do not attempt to post the question again.
    I have removed all the extra posts.

    This will work regardless of where your data is
    Code:
    Sub SplitTranspose()
    
       Dim Ary As Variant
       Dim i As Long, j As Long
       
       Ary = Selection
       Selection.ClearContents
       For i = 1 To 2
          For j = 0 To UBound(Split(Ary(i, 1), "/"))
             Selection.Offset(j, i - 1).Resize(1, 1).Value = Split(Ary(i, 1), "/")(j)
             Selection.Offset(j, 2).Resize(1, 1).Value = Split(Ary(1, 1), "/")(j) & " " & Split(Ary(2, 1), "/")(j)
          Next j
       Next i
    End Sub
    Just make sure that the 2 cells containing the data are both selected, before running the macro
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need formula or script to devide text

    Hi @Fluff,

    Sorry for my multiple posts. I thought i may lost that long reply post so i tried to send in multiple posts as i am not sure about approval.

    coming to code yes, it works perfectly but the output is bit confusing as it clears the selection also.
    If possible could you please keep the source data? and finally i just need concatenated name.

    Ex: If i keep two lines data in D9 D10, output starts from D9 E9 F9. Where F9 is concatenated name.

    So finally Can you keep source data and Concatenated name starts from F9 cell as output?

    IMP : Can we make this macro to run immediately(automatically) after pasting my second cell data?( i mean without using any short cut or ALT F8)

    Thanks in advance.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need formula or script to devide text

    If you're pasting data into D9:D10 & want the output to start in D9, then it's going to overwrite the original data.

    Will you always be pasting the data into the same cells, or could it be anywhere on the sheet?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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