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

Separate numbers from text

S

sms2luv

Member
Hi, I have a excel sheet with text like FTT_0864_Shahin_Devis.
I used Left and right formula combination and got the numbers
But for some people the name is different like.
FTT_CIA_6895_Tom_Harry.
How can I use same formula for both of them.
 
FIND ({1,2,3,4,5,6,7,8,9,0}, A1 & 1/17)
Click to expand...
Bosco, good afternoon, could you please explain to me that you use this part of the chain, and specifically "A1 & 1/17", and not for example "A1 & 1/5", or "A1 & 1/100", or any other number.
Thanks for your attention
Greetings Jorge
 
callao2908 said:
Bosco, good afternoon, could you please explain to me that you use this part of the chain, and specifically "A1 & 1/17", and not for example "A1 & 1/5", or "A1 & 1/100", or any other number.
Thanks for your attention
Greetings Jorge
Click to expand...
Hi ,

1/17 contains all the digits from 0 through 9.

You can also use the following version of the same formula , which is self-explanatory :

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

The highlighted portion is the change.

In case you wish to know why this is necessary , let me know.

Narayan
 
callao2908 said:
Bosco, good afternoon, could you please explain to me that you use this part of the chain, and specifically "A1 & 1/17", and not for example "A1 & 1/5", or "A1 & 1/100", or any other number.
Thanks for your attention
Greetings Jorge
Click to expand...
In additional to Narayan's explanation

1] 1/17 =0.0588235294117647 which is called Pandigital number that contains digits from 0 to 9

2] A1&1/17 is a short form of A1&1234567890 used to reduce the formula length.

3] Other Pandigital number can also be used :

A1&5^19

A1&7^18

A1&3^45

Regards
Bosco
 
Last edited:
Team,
I was using thr formula =0+MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),4)
and it was working fine to extra 4 digit numbers from text like FTT_CIA_6895_Tom_Harry
Need 1 more help, there are some new ids which has only 3 digits numbers like FTT_CIA_895_Tom_Harry
 
Svmaxcel said:
Team,
I was using thr formula =0+MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),4)
and it was working fine to extra 4 digit numbers from text like FTT_CIA_6895_Tom_Harry
Need 1 more help, there are some new ids which has only 3 digits numbers like FTT_CIA_895_Tom_Harry
Click to expand...
Formula I've posted will work as it is based on delimiter:

=TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,"_",REPT(" ",99))&"0123456789",1)),99))
 
You must log in or register to reply here.
Back
Top