Formula to copy a portion of one cell to another

[grampygrumpy]

I am looking to select and copy a portion of one cell to an adjacent cell.

Example: Did the device work (Yes)

I want to move the portion of the cell in the parenthesis "(Yes)" to another cell. I would also like to delete in the original cell the data that was moved.

Thank you

 

[Joe4]

Welcome to the Board!

The formula to extract just the (Yes) would look:

=MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1)

And the formula for the other portion would look like:

=TRIM(LEFT(A1,FIND("(",A1)-1))

Unless you do some manual steps (like create the formulas, do a Copy -> Paste Special Values, and then delete the original) column, this cannot all be done with one or two simple formulas.
If you want all that to happen automatically, you will need to create VBA. Are you open to that idea?
If so, please let us know the location of these values, and where you want the other values to be pasted to. And, is this being done on one cell, or a whole column?

And lastly, is there ever anything after the parentheses? If so, how should it be split up in that scenario?

 

[Joe4]

Here is some VBA code that will work for entries in column A, putting the split piece in column B:

Sub MySplit()

    Dim lastRow As Long
    Dim cell As Range
    Dim LArray() As String

    Application.ScreenUpdating = False
    
'   Find lastRow in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all cells in column
    For Each cell In Range("A1:A" & lastRow)
        If InStr(cell, "(") > 0 Then
            LArray = Split(cell, "(")
            cell.Offset(0, 1) = "(" & LArray(1)
            cell = Trim(LArray(0))
        End If
    Next cell
    
    Application.ScreenUpdating = True
        
End Sub

It is easy enough for any other column. Just change all the "A" references to whatever column you want it to run on.

 

[Rick Rothstein]

Assuming your item in parentheses is always at the end of the text (which is what everyone else has assumed based on your single example) and that you want to remove the space separating the opening parenthesis from the text before it after the split, then this VBA coded macro should work for you as well...

[table="width: 500"]
[tr]
	[td]Sub MySplit()
  With Columns("A")
    .Replace " (", Chr(1) & "(", xlPart
    .TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
  End With
End Sub[/td]
[/tr]
[/table]

The same provisos from Joe4's post about which column the code applies to applies to my code as well.

 

[grampygrumpy]

Thank you for your postings. I will give them a try. Have never attempted to use a macro, but always a first.

 

[Rick Rothstein]

Thank you for your postings. I will give them a try. Have never attempted to use a macro, but always a first.

See if this helps you get started...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.