extract text from cells

cuchi33

New Member
Joined
Aug 5, 2017
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
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​
….​
….​
….​
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Aug 5, 2017
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 5, 2017
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
thank you sir,
im reading how to install xl2bb and post vba codes using xl2bb
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top