The best answer for splitting text from a number depends on the details of the problem.

The best answer for splitting text from a number depends on the details of the problem.
Do you have a whole column of data to exchange? If so, make sure there are two adjacent blank columns. Type 12 in the adjacent cell. Repeat the process on the next rows. At a certain point, while you are typing, an artificial intelligence feature called Flash Fill in Excel 2013 and later will offer to perform the conversion for you. Click the light gray suggestions below the cell you are editing, and the remaining cells in the column will become populated with answers. You may replicate the procedure for the text portion of the results in the column to the right of the numbers.

If you need to perform the conversion in VBA code, you may use the Val function to capture the numbers at the beginning of the text. Val will also capture the sign and decimal point if present. You may then use Replace (among other methods) to give you the text without the leading numbers.

1. Dim nums As Double
2. Dim texts As String
3. nums = Val(Range(“A2”).Value)
4. texts = Replace(Range(“A2”).Value, nums, “”)

It is also possible to use regular expressions to return the text in your value. You access the regular expression feature via a reference to the VB Script library. I do it in the code below using late-binding. As a computer science graduate, I trust you need no explanation of regular expression patterns.

1. Dim texts As String
2. texts = SubString(Range(“A2”).Value, “\D+, 1)
4. Function SubString(text As String, Pattern As String, Instance As Long) As Variant
5. Static Regex As Object, oMatches As Object
6. SubString = “”
7. If Regex Is Nothing Then Set Regex = CreateObject(“VBScript.RegExp”)
8. With Regex
9. .Pattern = Pattern
10. If .test(text) Then
11. Set oMatches = .Execute(text)
12. If oMatches.Count <= Instance Then
13. SubString = oMatches(Instance – 1)
14. End If
15. End If
16. End With
17. End Function

You may also use worksheet formulas or the Data…Text to Columns ribbon item as discussed in the other answers to separate the numeric portion from the text.

By admin

Leave a Reply

Your email address will not be published.