• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Breaking alphanumeric character into two parts of uneven number

V

VDS

Member
Dear All,

I have created an excel file containing alphanumeric data like asp450, j123455, chandoo.org 12345 as per attachment and split the alphabets and numeric into two separate columns.

Here spacing between alpha and numeric are not fixed, (see excel file) and the numeric characters are fixed say (0 to 9), I tried to generate a formula myself by searching all the numbers and then applied COUNT with RIGHT(number of characters), in Column B.
in Column A, the alphabets have been separated. But the drawback of this formula is when any numeric character is repeated more than once, the formula gets affected. How it can be corrected ? or is there any better ways of doing it.

Help is requested.


VDS
 

Attachments

The text
B3: =LEFT(A3,MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A3)),"",FIND({1;2;3;4;5;6;7;8;9;0},A3)))-1)

The Number
C3: =RIGHT(A3,LEN(A3)-MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A3)),"",FIND({1;2;3;4;5;6;7;8;9;0},A3)))+1)

The first numeric is at position:
MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A3)),"",FIND({1;2;3;4;5;6;7;8;9;0},A3)))
 
You must log in or register to reply here.
Back
Top