Need formula or script to devide text

[signup]

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..!

 

[Fluff]

Hi & welcome to MrExcel
How about

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

 

[Youngdand]

Or without VBA.

You can copy the selection then pastespecial transpose.

 

[signup]

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.

Hi & welcome to MrExcel
How about

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

 

[Fluff]

How long are the strings in B1 & B2?

 

[Fluff]

Another option, try this instead

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

 

[signup]

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)

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]



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 !

Another option, try this instead

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

 

[Fluff]

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

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

 

[signup]

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.

 

[Fluff]

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?