separation of numbers and text

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
sir please send me e mail address so that i can send excel data.
my e mail is (e-mail address removed - moderator)
 
Last edited by a moderator:
Upvote 0
sir please send me e mail address so that i can send excel data.
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.
 
Last edited by a moderator:
Upvote 0
My data is like phone Book backup. for example, kashif03008652248
Assuming that value is in cell A1, put these formulas in the indicated cells and copy down as needed...

B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

C1: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)
 
Upvote 0
kashifzia423,

Here is a macro solution for your consideration.

Sample raw data:


Excel 2007
ABC
1kashif03008652248
2
Sheet1


After the macro:


Excel 2007
ABC
1kashif03008652248kashif3008652248
2
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below macro code, and, function
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

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

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetTextNumbers macro.
 
Upvote 0
Hello,

For the sake of providing multiple approaches to tackle the same issue, these are my macros (Not so brillant as RegExp expressions from Hiker 95 but .... ) :

Function Numbers (txt As Range)


Dim nb As Integer
Dim i As Integer
Dim temp As String
Dim a As Integer
nb = Len(txt)

For i = 1 To nb
Select Case Mid(txt, i, 1)

Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
temp = temp & Mid(txt, i, 1)

End Select

Next i

chiffres = temp

End Function

For retrieving text, same approach with a like difference :

Function alphabeta (txt As Range)


Dim nb As Integer
Dim i As Integer
Dim temp As String
Dim a As Integer

nb = Len(txt)

For i = 1 To nb
Select Case Mid(txt, i, 1)

Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9

Case Else
temp = temp & Mid(txt, i, 1)

End Select

Next i

test = Trim(temp)





End Function
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top