Forum Discussion
Search Formula help!
Help you lovely genius people.
Is there a formula to check a text string in a cell and if it contains a sequence of 4 or 5 numbers in a row return the 4 or 5 digit number?
For some backstory... I'm looking at a 2 columns of data from Xero and within some of the cells there will be a Job reference, but the text strings are not in any specific order/format. I was using this formula before...
=IF(M1824="Expense",IFERROR(UPPER(MID(E1824,SEARCH("JOB",E1824,1),3)&"-"&MID(E1824,SEARCH("JOB",E1824,1)+4,4)),UPPER(MID(D1824,SEARCH("JOB",D1824,1),3)&"-"&MID(D1824,SEARCH("JOB",D1824,1)+4,4))),"")
Columns D & E contained the text and this returned a result in a format I could then use for lookups i.e. "JOB-####", or an error if "JOB" wasn't in either column. This meant a lot of manual checking of error cells.
To add to the fun, we have just breached JOB-9999 so I'm now also having to look for JOB-10000+ (I don't envision having to worry about 6 digit job reference but if there is a way to future proof then bonus.)
Annoyingly, some of the data is system generated when invoices are processed and POs are matched, but some comes from expense claims and is manually entered, so although Xero will always give the reference as "JOB-####" or "JOB-#####" sometimes the reference will appear as "Job ####" or "job no ####" or just "####" or any other random combination the engineers think to put on their expenses, so if i can just reference a cell and pull back any 5 digit number and if there's no 5 digit number then look for a 4 digit number?
I hope someone out there can help, I've tried a few things but can't seem to get anywhere close to a useable result.
Living in hope, Marcus
If you are on Excel 365 Current channel, when as variant
6 Replies
If you are on Excel 365 Current channel, when as variant
Super Sergei to the rescue, thank-you very much sir! REGEXEXTRACT is a new one to me but works a treat, also as it's a formula it allows me to build into a complex IF statement which is ideal.
EmJayLepp , glad it helped
try this:
=LET(in, A1, numSets, TEXTSPLIT(in,TEXTSPLIT(in,SEQUENCE(10,,0),,1)), numSetLen, LEN(numSets), FILTER(numSets,numSetLen=MAX(numSetLen)))although MAX(numSets) would be easier, it would return 999 instead of 000023
but if that isn't a concern you could try
=LET(in, I10, numSets, TEXTSPLIT(in,TEXTSPLIT(in,SEQUENCE(10,,0),,1)), MAX(numSets))this of course is all assuming you don't have other even larger numbers appearing in those cells/fields. If so I would go back to the above version and in the FILTER also filter to be only LEN =4 or 5
I'm assuming this is a macro? Won't lie, I'm a bit of a novice when it comes to macros. How would this be applied so that I'd end up with a separate column with the 4 or 5 digit numbers?
No these are excel formulas. They can be drag drop / copy or you can create a dynamic array but in this case you would need to wrap it with a BYROW( ... LAMBDA ( ... ) ) like this:
=LET(arr, A1:A13, BYROW(arr, LAMBDA(in, LET( numSets, TEXTSPLIT(in,TEXTSPLIT(in,SEQUENCE(10,,0),,1)), numSetLen, LEN(numSets), FILTER(numSets,numSetLen=MAX(numSetLen)) ))))there is also some error catching that could be done like if you have 2 or more of the same length number strings or no number or only number inputs but I won't spend time on those as Sergei has provided a better solution. But wanted to address your question.
