hi all,
i need to search word was begin with "IMXXXXXXX" and begin with 7 digit within text column.
i 've tried with attached file but not work.
please kindly helps.
thanks in advances.
hi all,
i need to search word was begin with "IMXXXXXXX" and begin with 7 digit within text column.
i 've tried with attached file but not work.
please kindly helps.
thanks in advances.
May be try
Formula:Please Login or Register to view this content.
or With condition
Formula:Please Login or Register to view this content.
Last edited by shukla.ankur281190; 09-15-2017 at 06:30 AM.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
OH VERY GOOD.
but why used 99?
Rept(" ",99) will add 99 space between each space so that Mid function can easily extract the desired value.
I have give an other option if length of searched value is not = 9 then it should return "N"
Hope this helps
Neither of those work if there is an "IM" earler in the text string....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
can there be other 9-digit numbers elsewhere in the string?
Hi this formula is so difficult . haha
and i 'd like to know too that can be other format ? or this formular need only IMXXXXXXXXX follow only numberic digit right?
and for my formula as attached file can be modified as this results?
thanks.
Please answer my questions at Posts 5 and 6.
Hi shukla.ankur281190
I refer from old topic I've try to use your code as
It 'work and to show IMXXXXXXX follow by 7 digitsPlease Login or Register to view this content.
but there are many row to use this formular but not work when found "IMXXXXXXX" and to show 'N'
Please kindly help for this?
thanks a lot.
Try this in B2:
=MID(A2,MATCH(1,INDEX((MID(A2,ROW(INDIRECT("1:"&LEN(A2)-8)),2)="IM")
*ISNUMBER(--MID(A2,ROW(INDIRECT("3:"&LEN(A2)-6)),7)),0),0),9)
it' work thank you so much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks