kashifzia423
New Member
- Joined
- Dec 12, 2014
- Messages
- 8
sir please tell me how can i make separation between text and numbers from a same cell?
Please show us several representative samples of the kind of cell values you have to deal with and then show us how you want those sample values broken apart.sir please tell me how can i make separation between text and numbers from a same cell?
If I do that, then only I get to see your data and other volunteers here cannot participate to help... and you should want them to help so that you can get the best possible answer o your question. Besides, we do not need to see your actual full data set... what we need is just some representative samples of your data so we can see if the digits are altogether (next to each other) or separated (for example, like this... ab123cde456fgh maybe). If always together, we need to know if the number is always at the beginning or always at the end... if neither, whether there is some symbol (like a dash, colon, etc.) in front of it, or perhaps the number always appears in brackets.. If the digits are separated, we need to know how you want them retrieved... all mashed together, with a space or symbol between them or some other way. The problem is you are so familiar with your data that you do not see the many ways your original description can be interpreted by someone not familiar with your data... so we need you to give us a sense of what your data actually looks like so we can see how to construct a solution for you.sir please send me e mail address so that i can send excel data.
Assuming that value is in cell A1, put these formulas in the indicated cells and copy down as needed...My data is like phone Book backup. for example, kashif03008652248
Excel 2007 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | kashif03008652248 | ||||
2 | |||||
Sheet1 |
Excel 2007 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | kashif03008652248 | kashif | 3008652248 | ||
2 | |||||
Sheet1 |
Sub GetTextNumbers()
' hiker95, 12/26/2014, ME823773
Dim c As Range, lr As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
c.Offset(, 1) = TextNum(c, 1)
c.Offset(, 2) = TextNum(c, 0)
Next c
Columns("B:C").AutoFit
Application.ScreenUpdating = True
End Sub
Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
' jindon
' =TextNum(A1,1)
' 1 for Text only, 0 for Numbers only
With CreateObject("VBScript.RegExp")
.Pattern = IIf(ref = True, "\d+", "\D+")
.Global = True
TextNum = .Replace(txt, "")
End With
End Function