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

Need formula

M

maddy29

New Member
Hi There,

can you help me to get the result...I have a problem where I want to separate numeric and alphabetic value from one particular cell.

in a cell value is like - Independenceday04072016.. I want to separate these via formula.

I tried with-
A B
Independenceday04072016. = min(find({0,1,2,3,4,5,6,7,8,9},& A,"0123456789"})

but it gives me only position... Please suggest.

Thanks...
 
Once you have the position with this formula (in b2)
=MIN(FIND(ROW(1:10)-1,A2))

To get text:
=LEFT(A2, B2-1)

To get numbers:
=MID(A2, B2, 999)
 
If number can occur in middle of string, but is consecutive then.
=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),MATCH(2,1/MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+1-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

Confirmed as array (CSE).
 
A1 : Independenceday04072016

To get text :

=LEFT(A1,LEN(A1)-8)

To get number :

=RIGHT(A1,8)

Edit : since the right side number is a Date and always in 8 digits.

Regards
Bosco
 
Hi Maddy -

I suspect the part of the formula that's a bit confusing is the
FIND({1;2;3;4;5;6;7;8;9;0},A1)

the {1;2;3;4;5;6;7;8;9;0} is an array constant - you're asking Excel to find where each of those numbers appear in the string in A1. Excel returns the following for that line
{22;20;#VALUE!;17;#VALUE!;23;19;#VALUE!;#VALUE!;16}

i.e. the number 1 is found in position 22
2 in position 20
3 does not exist, so excel returns an #VALUE# error
4 in position 17
etc.
The ISERROR removes the #VALUE# and replaces it with "" - essentially nothing.

I've uploaded an explanation within the file for you. F9 within an excel formula evaluates it = you can see the result of the F9 in Col C - try it for yourself by hovering over the formula in A3 or A4 ....
We subtract 1 from the result to get the location of the character before the first digit

The MIN around the IF merely returns the smallest number form the resultant array in this case 15 - 16-1 ....

Hope this helps

D
 

Attachments

  • Maddy6 explanation.xlsx
    11.6 KB · Views: 3
David Evans said:
Hi Maddy -

I suspect the part of the formula that's a bit confusing is the
FIND({1;2;3;4;5;6;7;8;9;0},A1)

the {1;2;3;4;5;6;7;8;9;0} is an array constant - you're asking Excel to find where each of those numbers appear in the string in A1. Excel returns the following for that line
{22;20;#VALUE!;17;#VALUE!;23;19;#VALUE!;#VALUE!;16}

i.e. the number 1 is found in position 22
2 in position 20
3 does not exist, so excel returns an #VALUE# error
4 in position 17
etc.
The ISERROR removes the #VALUE# and replaces it with "" - essentially nothing.

I've uploaded an explanation within the file for you. F9 within an excel formula evaluates it = you can see the result of the F9 in Col C - try it for yourself by hovering over the formula in A3 or A4 ....
We subtract 1 from the result to get the location of the character before the first digit

The MIN around the IF merely returns the smallest number form the resultant array in this case 15 - 16-1 ....

Hope this helps

D
Click to expand...


David, thanks aton for your explanation.. I will definitely try to use the logic in some other example to understand it practically... I will keep you posted for any doubt...

thanks again :)
 
You must log in or register to reply here.
Back
Top