Hi
Need an excel formula that gets the 7 digits ,includes0, on these cells
Input:
dsfdsfdsfs434fdfdfdfdf (XX 0076846 / 1508)
reewrwr XX 0003431 ngfdsfsdfsdfdsfdsfdsfdsfdsfsdfs
1232 dsadasd 234 2ewq
start=00062015, end=20062015
start=00062015, end=20062015 xx 1112233
Output
0076846
0003431
Nothing found
Nothing found
1112233
=IFERROR(MID(A1,FIND("XX",UPPER(A1))+3,7),"Nothing found")
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Thanks! the code above works
is there another way to get numbers without relying on the "XX".
Probably a User Defined Function (VBA) which looks for 7 digit strings (assuming that there are no other 7 digit strings that you don't want). Not sure it can be done with formulas.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
UDF solutionIt returns the string as a number, not text.
Please Login or Register to view this content.
I use this formula in the cell
=IF(Num7Digit(A1)=0, "Not Found", Num7Digit(A1))
Last edited by ChemistB; 01-07-2016 at 10:39 AM.
I use:
=MID(A1,MATCH(9,MMULT(ABS(ISNUMBER(0+MID(MID("ζ"&A1&"ζ",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),9),{1,2,3,4,5,6,7,8,9},1))-{1,0,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1;1}),0),7)
for this type of query, though code is obviously less convoluted.
Regards
Xor XL,
Your formula didn't work on my uploaded sheet.
EDIT: Nevermind, I found my problem. Looks good!
Thanks, everyone it worked
Try this array formula (Confirm with Shift+Ctrl+Enter)Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks