separeting a string
 

hello. I have a column where the each cell consists
strings like,

column
----------------
12
3/4
2-7
3b
8/n
L45

I have alot of such cells possible more than 10000
cells in the column. Now i want to separate each string untill not
finding a numeric. For example, for the above cells, i want to have two
such columns:
columns1 columns2
--------------- ---------------
12
3 /4
2 -7
3 b
8 /n
L45
How can i do such a thing? Thanks lot.

separeting a string
 

one way

Sub findnonnum()
For Each c In Selection
mc = 0
For i = 1 To Len(c)
If Not IsNumeric(Mid(c, i, 1)) Then Exit For
mc = mc + 1
Next i
c.Offset(, 1) = Left(c, mc)
c.Offset(, 2) = Right(c, Len(c) - mc)
Next c
End Sub

--
Don Guillett
SalesAid Software

"oercim" wrote in message
oups.com...
hello. I have a column where the each cell consists
strings like,

column
----------------
12
3/4
2-7
3b
8/n
L45

I have alot of such cells possible more than 10000
cells in the column. Now i want to separate each string untill not
finding a numeric. For example, for the above cells, i want to have two
such columns:
columns1 columns2
--------------- ---------------
12
3 /4
2 -7
3 b
8 /n
L45
How can i do such a thing? Thanks lot.

separeting a string
 

try this one just select ur column and then run kode
splitting the numerich part - the rest im working on it


Sub test()

Dim rk, col, t, x, v, myVal
col = ActiveCell.Column
rk = Cells(65500, col).End(xlUp).Row
For t = 1 To rk
x = Cells(t, col)
For v = 1 To Len(x)
If Not IsNumeric(Mid(x, v, 1)) Then Exit For
myVal = myVal & Mid(x, v, 1)
Next
Cells(t, col + 1) = myVal: myVal = ""
Next

End Sub





"oercim" skrev:

hello. I have a column where the each cell consists
strings like,

column
----------------
12
3/4
2-7
3b
8/n
L45

I have alot of such cells possible more than 10000
cells in the column. Now i want to separate each string untill not
finding a numeric. For example, for the above cells, i want to have two
such columns:
columns1 columns2
--------------- ---------------
12
3 /4
2 -7
3 b
8 /n
L45
How can i do such a thing? Thanks lot.

separeting a string
 

got it now - Don leaded me in the right direction :-)


Sub test()

Dim rk, col, t, x, v, myVal
col = ActiveCell.Column
rk = Cells(65500, col).End(xlUp).Row
For t = 1 To rk
x = Cells(t, col)
For v = 1 To Len(x)
If Not IsNumeric(Mid(x, v, 1)) Then Exit For
myVal = myVal & Mid(x, v, 1)
Next
Cells(t, col + 1) = myVal ': myVal = ""
Cells(t, col + 2) = Right(x, Len(x) - Len(myVal))
myVal = ""
Next

End Sub



"excelent" skrev:

try this one just select ur column and then run kode
splitting the numerich part - the rest im working on it


Sub test()

Dim rk, col, t, x, v, myVal
col = ActiveCell.Column
rk = Cells(65500, col).End(xlUp).Row
For t = 1 To rk
x = Cells(t, col)
For v = 1 To Len(x)
If Not IsNumeric(Mid(x, v, 1)) Then Exit For
myVal = myVal & Mid(x, v, 1)
Next
Cells(t, col + 1) = myVal: myVal = ""
Next

End Sub





"oercim" skrev:

hello. I have a column where the each cell consists
strings like,

column
----------------
12
3/4
2-7
3b
8/n
L45

I have alot of such cells possible more than 10000
cells in the column. Now i want to separate each string untill not
finding a numeric. For example, for the above cells, i want to have two
such columns:
columns1 columns2
--------------- ---------------
12
3 /4
2 -7
3 b
8 /n
L45
How can i do such a thing? Thanks lot.

separeting a string
 

Thanks a lot Don and Excellent
excelent wrote:
got it now - Don leaded me in the right direction :-)


Sub test()

Dim rk, col, t, x, v, myVal
col = ActiveCell.Column
rk = Cells(65500, col).End(xlUp).Row
For t = 1 To rk
x = Cells(t, col)
For v = 1 To Len(x)
If Not IsNumeric(Mid(x, v, 1)) Then Exit For
myVal = myVal & Mid(x, v, 1)
Next
Cells(t, col + 1) = myVal ': myVal = ""
Cells(t, col + 2) = Right(x, Len(x) - Len(myVal))
myVal = ""
Next

End Sub



"excelent" skrev:

try this one just select ur column and then run kode
splitting the numerich part - the rest im working on it


Sub test()

Dim rk, col, t, x, v, myVal
col = ActiveCell.Column
rk = Cells(65500, col).End(xlUp).Row
For t = 1 To rk
x = Cells(t, col)
For v = 1 To Len(x)
If Not IsNumeric(Mid(x, v, 1)) Then Exit For
myVal = myVal & Mid(x, v, 1)
Next
Cells(t, col + 1) = myVal: myVal = ""
Next

End Sub





"oercim" skrev:

hello. I have a column where the each cell consists
strings like,

column
----------------
12
3/4
2-7
3b
8/n
L45

I have alot of such cells possible more than 10000
cells in the column. Now i want to separate each string untill not
finding a numeric. For example, for the above cells, i want to have two
such columns:
columns1 columns2
--------------- ---------------
12
3 /4
2 -7
3 b
8 /n
L45
How can i do such a thing? Thanks lot.

separeting a string
 

glad to help

--
Don Guillett
SalesAid Software

"oercim" wrote in message
oups.com...
Thanks a lot Don and Excellent
excelent wrote:
got it now - Don leaded me in the right direction :-)


Sub test()

Dim rk, col, t, x, v, myVal
col = ActiveCell.Column
rk = Cells(65500, col).End(xlUp).Row
For t = 1 To rk
x = Cells(t, col)
For v = 1 To Len(x)
If Not IsNumeric(Mid(x, v, 1)) Then Exit For
myVal = myVal & Mid(x, v, 1)
Next
Cells(t, col + 1) = myVal ': myVal = ""
Cells(t, col + 2) = Right(x, Len(x) - Len(myVal))
myVal = ""
Next

End Sub



"excelent" skrev:

try this one just select ur column and then run kode
splitting the numerich part - the rest im working on it


Sub test()

Dim rk, col, t, x, v, myVal
col = ActiveCell.Column
rk = Cells(65500, col).End(xlUp).Row
For t = 1 To rk
x = Cells(t, col)
For v = 1 To Len(x)
If Not IsNumeric(Mid(x, v, 1)) Then Exit For
myVal = myVal & Mid(x, v, 1)
Next
Cells(t, col + 1) = myVal: myVal = ""
Next

End Sub





"oercim" skrev:

hello. I have a column where the each cell consists
strings like,

column
----------------
12
3/4
2-7
3b
8/n
L45

I have alot of such cells possible more than 10000
cells in the column. Now i want to separate each string untill not
finding a numeric. For example, for the above cells, i want to have
two
such columns:
columns1 columns2
--------------- ---------------
12
3 /4
2 -7
3 b
8 /n
L45
How can i do such a thing? Thanks lot.