• 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.

Split Alphanumeric

Ehtisham Ali

Ehtisham Ali

Member
Hi,

I want to split contents of a cell in two different coloumns using excel only no VBA.

e.g

ali123 ---> ali(col1) and 123(col2)
matt.ferrell786 ---> matt.ferrell(col1) and 786(col2)
awesome998907 ---> awesome(col1) and 998907(col2)

Contents of cell may be of any length.

Regards
Ali
 
Ehtisham Ali said:
Hi,

I want to split contents of a cell in two different coloumns using excel only no VBA.

e.g

ali123 ---> ali(col1) and 123(col2)
matt.ferrell786 ---> matt.ferrell(col1) and 786(col2)
awesome998907 ---> awesome(col1) and 998907(col2)

Contents of cell may be of any length.

Regards
Ali
Click to expand...
Hi,

With this in A1

ali123

This in B1

=SUBSTITUTE(A1,C1,"")

This in C1

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))


Drag both down as required.
 
Last edited:
Hi Mate -

Assuming that ali123 is in cell A2...

For text use - MID(A2,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),"")),MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),"")))

For number use - MID(A2,MIN(IFERROR(FIND(ROW(INDIRECT("1:10"))-1,A2),"")),MAX(IFERROR(FIND(ROW(INDIRECT("1:10"))-1,A2),""))-MIN(IFERROR(FIND(ROW(INDIRECT("1:10"))-1,A2),""))+1)
 
hi Asheesh

MID(A2,MIN(IFERROR(FIND(ROW(INDIRECT("1:10"))-1,A2),"")),LEN(A2))

this one is giving error....try it on ali123
 
Hi Mike,

Could you please explain the functionality of FIND in your below formula

MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))
It is working fine but i am not getting how it is working.
 
Hi Ali,

Another solution.

With you data in A1 and down, try below formula's to extract characters & numbers:

Data in A1: Ali123
Alphabets in B1: =LEFT(A1,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),9.99999E+307))-1)
Confirm with Ctrl+Shift+Enter.
Numbers in C1: =MID(A1,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),9.99999E+307)),255)
Confirm with Ctrl+Shift+Enter.

Copy down both the formula's.

Regards,
 
Ehtisham Ali said:
Hi Mike,

Could you please explain the functionality of FIND in your below formula

MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))
It is working fine but i am not getting how it is working.
Click to expand...
Hi,

Of course i'll explain. Say we have this in A1

qwe1234

What the formula does is look for the first number but that's complicated by the fact that all numeric digits 0 thru 9 may not be present in the number, so if we simply did this

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1)),LEN(A1))

then the formula would fail on the above string in a1. To get around this we concatenate all the digits 0-9 on to the cell we're looking at like this:-

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))

Now the FIND bit evaluates first and evaluates like this and shows the position in the string of all the numbers

=MID(A1,MIN({8,4,5,6,7,13,14,15,16,17}),LEN(A1))

Note it finds the zero at position 8 because the 0 wasn't really in the string we concatenated that and the 1 at position 4 which was in the string so will always be a smaller number than anything we concatenated. The rest of the formula is simple enough and the MIN evaluates next and finds the 4 then the mid part of the formula evaluates and we get this

=MID(A1,4,LEN(A1))
 
@Somendra Misra @Mike H.. or any other experts around..

Hope you guys are doing good....

Just in continuation to Ehtisham's question above...

I was just trying to build a dynamic formula to extract the numbers from a string...

Say in A1 - I have Ali12345 and I want the result in B1 i.e. 12345

Now in A2 - I have 1234Ali and again the result will be in B2 i.e. 1234

I already came up with a formula...but this is hefty...is there a way to shorten this..or a better formula driven solution...

MID(A1,MIN(IF(ISERR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"",ROW(INDIRECT("1:"&LEN(A1))))),MAX(IF(ISERR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"",ROW(INDIRECT("1:"&LEN(A1)))))-MIN(IF(ISERR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"",ROW(INDIRECT("1:"&LEN(A1)))))+1)
 
Yes very right Asheesh the same i was thinking about a dynamic formula which will work for below examples

ali123, 123ali, ehtisham123ali result must be 123

Regards
Ali
 
Hi Asheesh ,

If you go through the Formula Challenges section of this forum , you can find several approaches to this problem. One of them gives this formula , which is easy to understand :

=MID(A1,MIN(IFERROR(FIND(ROW($1:10)-1,A1 & "A"),"")),MAX(IFERROR(SEARCH((ROW($1:10)-1) & CHAR(COLUMN(BM:CL)),A1 & "A"),0))-MIN(IFERROR(FIND(ROW($1:10)-1,A1 & "A"),""))+1)

The three sections are colored differently so that they are easy to spot.

The second section , colored blue is brilliant thinking , but so easy to understand.

The formula is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
 
Somendra Misra said:
Hi @Asheesh / @Ehtisham Ali ,

Try below array formula:

=MID(A1,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),9.99999E+307)),MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),0))-MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),9.99999E+307))+1)

I am in process of reducing it.

Confirm with Cntrl+Shift+Enter.

Regards,
Click to expand...

Thanks Somendra
Nice one works perfect.

But I don't there is much use of 9.99999E+307 we can just use a '0' or "" for the sake of simplicity.

Regards
Ali
 
@Somendra Misra @NARAYANK991

They work flawless...superb..you guys are an ocean of knowledge..

Narayan Sir - I just removed Concatenated A from both Find functions in your formula and It worked fine...is there any particular reason that you added A..??
 
Hi Asheesh ,

Actually , the concatenation is not required in the first and third sections , since we are looking for a digit in those sections.

It is absolutely required in the middle section , since here what we are looking for is a two character combination which has a digit as the first character , and a letter as the second character. This signifies the end of the number. Thus , if the input is a string which consists of only digits , such as 123 , then the formula will fail. Concatenating the input string with a letter ( it can be any letter ) ensures that even in this situation , the formula will work correctly.

Narayan
 
You must log in or register to reply here.
Back
Top