How to split alphanumeric data

[arekay]

I have a alphanumeric data in a cell. The combination of these are not a sequence one. For ex: Cell A1 = Adhdhdhd33838
A2 = 9494AdkdkdkA
A3 = /skkfk448dkdk
What is the procedure to split the characters and numeric data seperately and store in a seperate cell. Provide the sample code and formula in case any to be used.

 

[Juan Pablo González]

Yes sir ! as you command !

Welcome to the board :wink:. May I ask what have you tried ? the procedure is simple, take each character in the string, see if its a number, if so, store it in a temp numeric string, if not, then store it in the 'other' temp alpha string, then, return both.

 

[Aladin Akyurek]

The following array-formula (which is entered using the key combination control+shift+enter, not just enter) would give the desired results. Note that the formula includes functions from the morefun.xll add-in, which you need download and install.

B1:

=MCONCAT(IF(ISNUMBER(SETV(MID(A1,INTVECTOR(LEN(A1),1,0),1))+0),"",GETV()))

C1:

=MCONCAT(IF(NOT(ISNUMBER(SETV(MID(A1,INTVECTOR(LEN(A1),1,0),1))+0)),"",GETV()))

aaSplitAlphaNumData arekay.xls
	A	B	C	D
1	Adhdhdhd33838	Adhdhdhd	33838
2	9494AdkdkdkA	AdkdkdkA	9494
3	/skkfk448dkdk	/skkfkdkdk	448
4
Sheet1

The URL for download is...

http://longre.free.fr/english/index.html
This message was edited by Aladin Akyurek on 2003-01-26 11:21

 

[solutions4ca]

Splitting Cells

Hello!

I am working on a list of users and email addresses, and would like to split the names:

John Doe


John, Doe

I have John Doe in one cell and would like to split it so John is in one cell and Doe is in the other! Any information on that would be greatful!

Thanks,

Solutions

 

[PaddyD]

solutions4ca,

You should start a new thread for your topic, not stick on the end of an existing one.

 

[brettdj]

Or with a couple of VBA user defined functions, you can use these like so


=RetNum(A1)
=RetNonNum(A1)

or

=RetNum("Adhdhdhd33838")
=RetNonNum("Adhdhdhd33838")

Function RetNum(AnyStr As String)
    Dim RegEx
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = "[^\d]+"
    End With
    RetNum = RegEx.Replace(AnyStr, "")
    Set RegEx = Nothing
End Function


Function RetNonNum(AnyStr As String)
    Dim RegEx
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = "[\d]+"
    End With
    RetNonNum = RegEx.Replace(AnyStr, "")
    Set RegEx = Nothing
End Function