Results 1 to 6 of 6

How to split alphanumeric data

This is a discussion on How to split alphanumeric data within the Excel Questions forums, part of the Question Forums category; I have a alphanumeric data in a cell. The combination of these are not a sequence one. For ex: Cell ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    1

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Yes sir ! as you command !

    Welcome to the board . 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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    66,228

    Default

    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()))

    ******** ******************** ************************************************************************>
    Microsoft Excel - aaSplitAlphaNumData arekay.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    Adhdhdhd33838Adhdhdhd33838*
    2
    9494AdkdkdkAAdkdkdkA9494*
    3
    /skkfk448dkdk/skkfkdkdk448*
    4
    ****
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    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 ]

  4. #4
    New Member
    Join Date
    Aug 2005
    Location
    Fresno
    Posts
    10

    Default 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

  5. #5
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    solutions4ca,

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

  6. #6
    Board Regular
    Join Date
    Feb 2003
    Location
    Adelaide, Australia
    Posts
    426

    Default

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


    =RetNum(A1)
    =RetNonNum(A1)

    or

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

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com