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

 

[signup]

I want to forward this sheet to my team. so i will instruct my team to make sure to copy the date in those particular two cells(Ex: D9:D10)

and i think you misunderstood my point in above post. I requested to remove first name and last name so Output Starts from F9 cell i.e. Concatenated Name. So there is no way to overwrite the original data because Original data in D9:D10 cells and concatenated names output in F9 cell. Did you got my point?

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)

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?

 

[Fluff]

How about this. It needs to go in the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim i As Long, j As Long
   Dim Ary As Variant
   
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("D10")) Is Nothing Then Exit Sub
   
   Ary = Target.Offset(-1).Resize(2)
   For i = 1 To 2
      For j = 0 To UBound(Split(Ary(i, 1), "/"))
         Target.Offset(j - 1, 2).Resize(1, 1).Value = Split(Ary(1, 1), "/")(j) & " " & Split(Ary(2, 1), "/")(j)
      Next j
   Next i
End Sub

 

[signup]

Hi @Fluff

Great It Works perfectly. Thank you so much for your help on this. Our team doing this all conversions manually from last two years. You saved many hours of time for all of our time.

I have another sheet to covert range of selection text to proper case. Can we use these two codes in single document? one is in Sheet1 and another one is Sheet2?

If we can use both codes in single document please advise how can we do that?

Thanks again

 

[Fluff]

If you want to convert the names to proper case try

         Target.Offset(j - 1, 2).Resize(1, 1).Value = Application.Proper(Split(Ary(1, 1), "/")(j)) & " " & Application.Proper(Split(Ary(2, 1), "/")(j))

 

[signup]

Hi Fluff,

Can you please shift the output to 3 (or 4) cells down in same column? i tried to change target.offset values but no luck.

Ex: now, as per your code output names starts from F9, Can you please change it to F12 or F13 ?

Thanks,
Signup

How about this. It needs to go in the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim i As Long, j As Long
   Dim Ary As Variant
   
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("D10")) Is Nothing Then Exit Sub
   
   Ary = Target.Offset(-1).Resize(2)
   For i = 1 To 2
      For j = 0 To UBound(Split(Ary(i, 1), "/"))
         Target.Offset(j - 1, 2).Resize(1, 1).Value = Split(Ary(1, 1), "/")(j) & " " & Split(Ary(2, 1), "/")(j)
      Next j
   Next i
End Sub

 

[signup]

No Fluff, I not taking about this code. Its OK to me these output names can be in proper or Upper based on my source text case.

apart from this, I have a macro in another sheet to convert the range of selected text to proper case by clicking on a button. So can i use these both codes in same workbook(one in Sheet 1 & another one in sheet 2). is there any chance like that?

This is the code i amusing now :

Private Sub CommandButton1_Click()

Dim rng As Range, cell As Range
Set rng = Selection

For Each cell In rng
    If Not cell.HasFormula Then
        cell.Value = WorksheetFunction.Proper(cell.Value)
    End If
Next cell

End Sub

In a same way, what i should modify in above code to change the selected case text to UPPER CASE? i will insert one more button and will place the code for UPPER CASE. Is it ok to do that?

If you want to convert the names to proper case try

         Target.Offset(j - 1, 2).Resize(1, 1).Value = Application.Proper(Split(Ary(1, 1), "/")(j)) & " " & Application.Proper(Split(Ary(2, 1), "/")(j))

 

[Fluff]

This will start the output in F12

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim i As Long, j As Long
   Dim Ary As Variant
   
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("D10")) Is Nothing Then Exit Sub
   
   Ary = Target.Offset(-1).Resize(2)
   For i = 1 To 2
      For j = 0 To UBound(Split(Ary(i, 1), "/"))
         Target.Offset(j + 2, 2).Resize(1, 1).Value = Split(Ary(1, 1), "/")(j) & " " & Split(Ary(2, 1), "/")(j)
      Next j
   Next i
End Sub

and in your other code change this for upper case

cell.Value = Ucase(cell.Value)