Hi,

I have an Excel spreadsheet with about 17000 lines.

In certains cells of col B, there is a string starting with "(If ISCunable to resolve, " then the rest of the string may differ.

I need to cut the string out of the cell, and paste it into the cell next to it (col C).

I found some code for matching strings, and copying to a new sheet, but I have no idea how to adapt it, it's chinese to me! :-)

Any help much appreciated.

I'd suggest sticking with Excel on this one.  You can easily do this with a Macro.  Here is the macro code that worked for me:

Sub Macro1()
  Dim MatchString As String
  MatchString = "(If ISCunable to resolve,"
  For Counter = 1 To Range("B:B").Count
    If (Left(Range("B" & Counter).Value, Len(MatchString)) = MatchString) Then
      Range("B" & Counter).Select
      Selection.Cut
      Range("C" & Counter).Select
      ActiveSheet.Paste
    End If
  Next Counter
End Sub

• Edited by jrjespersen Thursday, February 07, 2013 11:15 PM
• Marked as answer by Aiden_CaoMicrosoft community contributor, Moderator Wednesday, February 20, 2013 2:58 AM

You probably don't need to go to the extent of implementing VBA code as that would definitely be a complicated thing to have to do.

I knocked up an Excel formula for you that will take care of it

=IF(ISERROR(SEARCH("(If ISCunable to resolve, ";A1;1));"";TRIM(RIGHT(A1;(LEN(A1)+1)-(SEARCH("(If ISCunable to resolve, ";A1;1)+LEN("(If ISCunable to resolve, ")))))

All you have to do is change the reference cells (the A1's) and strings you're searching for (make sure you change the string in all three places). Once you've done that, copy and paste it all the way down the spread sheet and you're done. If you want the resulting values grouped together, you can simply set a column filter.

• Edited by Nigel Finn Friday, February 08, 2013 12:33 AM
• Proposed as answer by Nigel Finn Friday, February 08, 2013 8:50 PM
• Marked as answer by Aiden_CaoMicrosoft community contributor, Moderator Wednesday, February 20, 2013 2:57 AM

I'd suggest sticking with Excel on this one.  You can easily do this with a Macro.  Here is the macro code that worked for me:

Sub Macro1()
  Dim MatchString As String
  MatchString = "(If ISCunable to resolve,"
  For Counter = 1 To Range("B:B").Count
    If (Left(Range("B" & Counter).Value, Len(MatchString)) = MatchString) Then
      Range("B" & Counter).Select
      Selection.Cut
      Range("C" & Counter).Select
      ActiveSheet.Paste
    End If
  Next Counter
End Sub

• Edited by jrjespersen Thursday, February 07, 2013 11:15 PM
• Marked as answer by Aiden_CaoMicrosoft community contributor, Moderator Wednesday, February 20, 2013 2:58 AM

I'd suggest sticking with Excel on this one.  You can easily do this with a Macro.  Here is the macro code that worked for me:

Sub Macro1()
  Dim MatchString As String
  MatchString = "(If ISCunable to resolve,"
  For Counter = 1 To Range("B:B").Count
    If (Left(Range("B" & Counter).Value, Len(MatchString)) = MatchString) Then
      Range("B" & Counter).Select
      Selection.Cut
      Range("C" & Counter).Select
      ActiveSheet.Paste
    End If
  Next Counter
End Sub

• Edited by jrjespersen Thursday, February 07, 2013 11:15 PM
• Marked as answer by Aiden_CaoMicrosoft community contributor, Moderator Wednesday, February 20, 2013 2:58 AM

You probably don't need to go to the extent of implementing VBA code as that would definitely be a complicated thing to have to do.

I knocked up an Excel formula for you that will take care of it

=IF(ISERROR(SEARCH("(If ISCunable to resolve, ";A1;1));"";TRIM(RIGHT(A1;(LEN(A1)+1)-(SEARCH("(If ISCunable to resolve, ";A1;1)+LEN("(If ISCunable to resolve, ")))))

All you have to do is change the reference cells (the A1's) and strings you're searching for (make sure you change the string in all three places). Once you've done that, copy and paste it all the way down the spread sheet and you're done. If you want the resulting values grouped together, you can simply set a column filter.

• Edited by Nigel Finn Friday, February 08, 2013 12:33 AM
• Proposed as answer by Nigel Finn Friday, February 08, 2013 8:50 PM
• Marked as answer by Aiden_CaoMicrosoft community contributor, Moderator Wednesday, February 20, 2013 2:57 AM

You probably don't need to go to the extent of implementing VBA code as that would definitely be a complicated thing to have to do.

I knocked up an Excel formula for you that will take care of it

=IF(ISERROR(SEARCH("(If ISCunable to resolve, ";A1;1));"";TRIM(RIGHT(A1;(LEN(A1)+1)-(SEARCH("(If ISCunable to resolve, ";A1;1)+LEN("(If ISCunable to resolve, ")))))

All you have to do is change the reference cells (the A1's) and strings you're searching for (make sure you change the string in all three places). Once you've done that, copy and paste it all the way down the spread sheet and you're done. If you want the resulting values grouped together, you can simply set a column filter.

• Edited by Nigel Finn Friday, February 08, 2013 12:33 AM
• Proposed as answer by Nigel Finn Friday, February 08, 2013 8:50 PM
• Marked as answer by Aiden_CaoMicrosoft community contributor, Moderator Wednesday, February 20, 2013 2:57 AM

Something like this?

$xl=New-Object -ComObject Excel.Application
$wb=$xl.Workbooks.Open('H:\test.xlsx')
$ws=$wb.WorkSheets.item(1)
$xl.Visible=$true

while($ws.Cells.Item($i, 1).Text -ne ''){
    $Rng = $ws.Cells.Item($i, 1) #row $i, Column 1
    If ($Rng.Text -match "(If ISCunable to resolve, "){
	$ws.Cells.Item($i, 2).Text=$ws.Cells.Item($i, 1).Text
	$ws.Cells.Item($i, 1).Text=''
}
    else{$i++}
    }

ello, I have a question to cut paste part of a cell starting from the letter P until end of the 3rd point in Pre-condition section. In other words Cut paste the entire Pre-condition section to another cell. There are around 1100 rows that I need to do this for and unlink the example quoted below, the number of characters in the Pre-condition section are irregular for each row. Can someone please help with the right formula to achieve this? Thank you very much in advance for your help.

Pre-condition:
1. xxxx xxxxx
2. xxxx xxxxx
3. xxxx xxxxx
Test Steps: 
1. xxxx xxxxx

What would be the code if I wanted to check for this string anywhere in the cell?

For example, if my cell contained "xxxxxx If ISCunable to resolve, yyyyy" then I would want this to be a match.

Also, I want to copy the only the 5 character of the matched value i.e. "If IS" to column C.

What would be the code if I wanted to check for this string anywhere in the cell?

For example, if my cell contained "xxxxxx If ISCunable to resolve, yyyyy" then I would want this to be a match.

Also, I want to copy the only the 5 character of the matched value i.e. "If IS" to column C.

This topic is closed.  If you still want to speak Chinese then see this: Search for you dream in any column

Please post new issues in new topic.  You can include a link back to this topic if you feel it is helpful.