Extracting values

[B.brazilian]

Hi folks

My question is pretty simple, I have on the column A values such as 1122, 1324, 1231, 1423, and so on. But from time to time, I'd get non-numeric values on that same column, for instance, 1123CB, 1200, 1509FB, 1212HB,1231, etc...

I'd like to know how to extract the alphanumeric values (ie. "CB", "FB", "HB") from those cells and place it in another column, say, column D.

Thanks in advance!

BR

 

[Rick Rothstein]

I think this code will do what you want...

Sub DeleterRowsWithTextInColumnA()
  Dim LastRow As Long
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Range("A" & StartRow & ":A" & LastRow).SpecialCells(xlCellTypeConstants, xlTextValues)
    .Copy Range("D" & StartRow)
    .Delete xlShiftUp
  End With
End Sub

The only thing you need to do is change the StartRow constant (the Const statement) to the row number containing your first piece of data (I assumed that would be Row 2 with Row 1 containing headers).

 

[B.brazilian]

Sorry Rick I guess i wasnt clear enough
I dont want to delete the whole thing, what i am trying to do is keep the numeric values (1123) as the are in the column "A", and move the non-numeric values (HB) to another column "D"

thanks man

 

[Rick Rothstein]

I dont want to delete the whole thing, what i am trying to do is keep the numeric values (1123) as the are in the column "A", and move the non-numeric values (HB) to another column "D"

Does that mean you want the numbers to remain in the same cells they currently are in? Also, the non-numeric values moved to Column D... are they supposed to remain on the same row they were in originally?

 

[B.brazilian]

Does that mean you want the numbers to remain in the same cells they currently are in? Also, the non-numeric values moved to Column D... are they supposed to remain on the same row they were in originally?

Yes, you got it! basically i want to do is move the "HB" that is in the end of the numbers to the column D, same rows and everything.

it would be something like the function "=Right(A2)" but the only thing is, this function wont remove the characters from the column A as i need it to.

 

[mikerickson]

If the number portions are all 4 digits long, you could use TextToColumns with a fixed width of 4 for the first column and not import the second col.

 

[Rick Rothstein]

Okay, give this code a try...

Sub DeleterRowsWithTextInColumnA()
  Dim LastRow As Long
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Range("A" & StartRow & ":A" & LastRow)
    .Copy Range("D" & StartRow)
    .SpecialCells(xlCellTypeConstants, xlTextValues).Clear
    .Offset(0, 3).SpecialCells(xlCellTypeConstants, xlNumbers).Clear
  End With
End Sub

 

[mikerickson]

If the number of numeric characters is variable, try this CSE formula

=LEFT(E1, MIN(FIND(CHAR(ROW(59:250)), E1&CHAR(ROW(59:250))))-1)+0