Extract string from cells

[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.

 

[Don Guillett]

Look in the help index for FIND and then for LEFT. Put em together.

 

[Nigel]

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

 

[XP]

I changed the "Mid" to "Left" and it worked great,

Thanks Nigel...

 

[JLGWhiz]

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

 

[Ken]

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