| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
separeting a string
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
| Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) | Excel Programming | |||
| Importing Long String - String Manipulation (EDI EANCOM 96a) | Excel Programming | |||
| to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
| Create a formula into a String then assign string to a cell | Excel Programming | |||
Linear Mode
