Extract string from cells

  • Thread starter Thread starter XP
  • Start date Start date
X

XP

Suppose I have data that looks something like:

1A-Data
1B5D-Data-4F
3RCDF-AA-Data
5L-L-Data

The strings may be any length, but I need to get any characters to the left
of the first dash in each string (row). So the above should return:

1A
1B5D
3RCDF
5L

The data resides in one column (col "I") and may be variable in the number
of rows. I would like the extracted info from each row written into the
adjacent column (Col "J").

Thanks much in advance for your example code.
 
Use the Instr function to look for the hash (-) and then take all chars left
of that position

e.g Range("J1") = Mid(Range("I1"),Instr(1,Range("I1"),"-")+1)

.... VBA code for variable length column

Dim xr As Long
With ActiveSheet
For xr = 1 To .Cells(.Rows.Count, "I").End(xlUp).Row
.Cells(xr, "J") = Mid(.Cells(xr, "I"), InStr(1, .Cells(xr, "I"), "-") +
1)
Next xr
End With
 
Run this on a trial sheet and see if it is what you are looking for.

Sub extrData()
Dim x As String
Dim n, lr As Long
Dim c As Range
lr = Cells(Rows.Count, 9).End(xlUp).Row
Set myRng = Range("I2:I" & lr)
With myRng
For i = 2 To lr
y = Len(Range("I" & i)) - InStr(Range("I" & i), "-")
n = Len(Range("I" & i)) - (y + 1)
x = Left(Range("I" & i), n)
Range("A" & i) = x
Next
End With
End Sub
 
If this is a one time thing you can simply copy column L to column J
and do a search and replace of -* with nothing.
Or you can build that into your code.

Ken
 
Back
Top