extract text from cells

[cuchi33]

i want to extract text from Cells in Column A and put it in Column C
Excel: C2= Right (A2;2)
VBA code i used:
Private Sub CommandButton1_Click()
Dim sh1 As Worksheet
Set sh1 = Sheets("Sheet1")
a = sh1.Range("A2:D" & sh1.Range("A" & Rows.Count).End(3).Row).Value2
For i = 1 To UBound(a, 1)
If Right(a(i, 1), 2) = "CP" Then
a(i, 3) = "CP"
End If
Next
End Sub
When I press the button, nothing happpened.

name​	number​	extracted text​
text1cp​	400​
bcncp​	401​
34454cp​	402​
cdffcp​	403​
….​	….​	….​

 

[Peter_SSs]

When I press the button, nothing happpened.

If that is the actual code you used and the actual data then there are at least 2 reasons that nothing happened.

1. If Right(a(i, 1), 2) = "CP" Then This is case-sensitive. Your code is looking for upper case "CP" but your cell values are lower case.
2. Your code does not write anything back to the worksheet so even if it was finding "cp" the array values would be updated in column 3 but not written aback to the worksheet.

Taking a bit of a guess, try this with a copy of your data.
Note: Good practice to declare all variables.

Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet
  Dim a As Variant
  Dim i As Long
  
  Set sh1 = Sheets("Sheet1")
  a = sh1.Range("A2:D" & sh1.Range("A" & Rows.Count).End(3).Row).Value2
  For i = 1 To UBound(a, 1)
    If UCase(Right(a(i, 1), 2)) = "CP" Then
      a(i, 3) = "CP"
    End If
  Next i
  sh1.Range("A2:D2").Resize(UBound(a, 1)).Value = a
End Sub

 

[cuchi33]

Dear sir,
you code works!
is there a way to extract specific text or number in cells in column A
for example:
A1: "Today is 7/8/2020 and i read posts in mrexcel.com"
is there a function to extract " read" or " 2020" or "mrexcel" ( extract anything from variable sized strings )

 

[Peter_SSs]

One example is not much to go on. Could you make up a small set of sample data - say 10-15 rows - and include the expected results (entered manually) and post all of that with XL2BB
so that we have a better idea of the variation in your data and just what results you expect?

 

[cuchi33]

thank you sir,
im reading how to install xl2bb and post vba codes using xl2bb