Hello Friends
In the cell A1 text input is given, in light yellow cells D2:D12 the 9 digit part number in the format of XXXX-XXXXX need to be extracted.
thanks in advance.
Hello Friends
In the cell A1 text input is given, in light yellow cells D2:D12 the 9 digit part number in the format of XXXX-XXXXX need to be extracted.
thanks in advance.
Sekar
Is this data representative: are there always 4 numbers?
1 is always the first in a group?
hi Sekars. try:
=IFERROR(MID($A$1,SEARCH("????-?????",$A$1,IFERROR(SEARCH(D1,$A$1),1)+10),10),"")
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
You can do that with PowerQuery add-in for Excel 2010
Last edited by sandy666; 09-28-2017 at 07:01 AM. Reason: file updated for more flexibility
Hello all
here the conditions is first 4 numbers then `-' hyphen the next 5 numbers will come. First number not always 1, it will be vary form 0 to 9, again the syntax is XXXX-XXXXX where X is 0 to 9.
but "-" always exist? if yes see post #5
D2=MID($A$1,SMALL(INDEX((ISNUMBER(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1)-9)),4)+0)+(MID($A$1,ROW(INDIRECT("5:"&LEN($A$1)-5)),1)="-")+ISNUMBER(MID($A$1,ROW(INDIRECT("6:"&LEN($A$1)-4)),5)+0)<>3)*10^3+ROW(INDIRECT("1:"&LEN($A$1)-9)),0),ROWS(D$2:D2)),10)
Try this and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thank to all who have replied for this post.
Benishiryo – It looks like circular looped, Numbers are endlessly going,
Sandy666 - Ito be frank I do know much about Power Query and need to google it and need to be used. Will try it.
Siva – It pulls texts also and again endless
Thanks again
it works for me see the attached file
yes, in your file its fine, but in real application it pulls the text also instead of 10 digit part numbers.
Please refer the attached file.
thanks again for your formula.
I copied only value from your last file and pasted into my file then refresh QueryTable
d2=MID($A$1,SMALL(INDEX((ISNUMBER(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1)-9)),4)+0)+(MID($A$1,ROW(INDIRECT("5:"&LEN($A$1)-5)),1)="-")+ISNUMBER(MID($A$1,ROW(INDIRECT("6:"&LEN($A$1)-4)),5)+0)<>3)*LEN($A$1)+ROW(INDIRECT("1:"&LEN($A$1)-9)),0),ROWS(D$2:D2)),10)
earlier Considered maximum text length as 10*3 = 1000 in your case it is around 7200 characters that's why problem occor
try above formula and copy towards down
Siva - Thanks, Now it works fine, but need to test in real application it will take more time. Hope no issues.
Thanks to all who replied / posted for my post.
Thanks again.
You are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks