Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Actually I want to split character & digits in a same cell, in Excel 2010

My cell is like this: test03ok
Another cell could be: 34test

I want to split the numbers from the letters, so my desired out put of the above 2 examples would be

test 03 ok
34 test

Can this be done in Excel without VBa or do I have to use VBa?

share|improve this question
    
I don't think this is possible with a formula or other default tools, but rather by using VBA. Would that be fine with you? –  Jerry Aug 16 '13 at 7:24
    
Okay Mike. But I assume that you can also have something like testing00002notok and you expect 'testing' '00002' and 'notok' separately, right? –  Jerry Aug 16 '13 at 7:27
    
@Mike Well, you can't do that with a formula or a tool available :( I think you can do that with a regex in VBA though (I'm not familiar with how to do this, but I could do some research and come up with something). –  Jerry Aug 16 '13 at 7:31
    
You can't do regular expressions per-se in Excel, see stackoverflow.com/questions/9744602/… and searchengineland.com/… for alternatives –  Dave Aug 16 '13 at 7:33
    
@Mike, let's delete some of the comments on this thread so it stays tidy. It makes it easier for other people reading it. I'm going to delete all of my comments since the post is now updated. –  Dave Aug 16 '13 at 9:13

1 Answer 1

up vote 0 down vote accepted

Here is a regex-based function that should do the trick. Paste it into a standard module that you've inserted from the VBA editor menu. You can access the editor by clicking Visual Basic on the Developer tab of the ribbon. Then choose Insert -> Standard Module on the menu.

You will also need to set a reference to the Microsoft VBScript Regular Expressions 5.5 library. To do that, click Tools -> References on the editor menu. Scroll down to find the listing and check-mark it to set the reference.

Regex experts would want to improve upon the pattern I used. It inserts extra spaces into some strings, which I successfully got rid of using the worksheet TRIM function.

Function ReNSpace(text As String)
    Dim regex As New RegExp
    regex.Pattern = "(?:(\D+)?(\d+))|(?:(\D+)(\d+)?)"
    regex.Global = True
    regex.IgnoreCase = True
    ' Test if a match is found
    If regex.Test(text) = True Then
        ReNSpace = WorksheetFunction.Trim(regex.Replace(text, "$1 $2 $3 $4"))
    Else
        ReNSpace = CVErr(xlErrValue)
    End If
    Set regex = Nothing
End Function
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.