Extract string from cells
 

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.

Extract string from cells
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"XP" wrote in message
...
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.

Extract string from cells
 

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

Regards,
Nigel




"XP" wrote in message
...
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.

Extract string from cells
 

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

Thanks Nigel...

"Nigel" wrote:

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

Regards,
Nigel




"XP" wrote in message
...
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.

Extract string from cells
 

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

"XP" wrote:

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.

Extract string from cells
 

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

On Jan 2, 3:34*pm, JLGWhiz wrote:
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



"XP" wrote:
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.- Hide quoted text -

- Show quoted text -