EXTRACTING the first four consecutive numbers and placing them in another cell?

[Robo]

Basically, column C is the tricky one. It comes from the bank and somewhere in there is a 4-digit tenant reference. I did a formula to try and isloate it and it worked on most but if you look at the very first row, there is a spurious 99 in there, so it didn't work. Is there a way of EXTRACTING the first four consecutive numbers and placing them in another cell?
Thanks
Robo

[DonkeyOte]

Are you able to install morefunc.xll ? This would allow you to use the Regex functions without need for a) UDF or b) complex formulae, eg using the morefunc REGEX.MID function:

H2: =REGEX.MID(A1,("[0-9]{4})")
copied down

Let us know...

re: morefunc:

Info: http://xcell05.free.fr/morefunc/english/
download: http://download.cnet.com/Morefunc/30...-10423159.html

[Robo]

Hi
No, our it dept wouldnt allow!

Robo

[DonkeyOte]

Hmmm... "computer says no" ... are you able to use VBA at all ?

[Robo]

Yes we can

[Robo]

what is morefunc.xll?

[DonkeyOte]

morefunc.xll is Laurent Longre's Add-In for XL which basically gives you a set of functions otherwise not available natively from XL - it's well known and well trusted and has some very useful & clever functions... it is also compiled in C (.xll) which means that it's pretty efficient (ie as fast as using normal XL functions), the add-in can also be "embedded" into files meaning you can distribute files using the morefunc functions to other users without them having to actually install the add-in themselves ... this is a very cool feature indeed (if you're into that kind of thing of course) as this is one of the normal drawbacks of standard .xla(m) add-ins.

[Robo]

Thanks for the info. My formua almost works though, I thought it could tweeked a bit?

[DonkeyOte]

Possibly but it won't be very efficient... for cleanliness I would opt for UDF, eg converting the RegExp approach into your own UDF:

Please Login or Register  to view this content.

Utilised from cell: =TENANT(C2)

[Robo]

i have just downloaded the software after talking with our it dept. Which is the easy route?

[DonkeyOte]

If VBA isn't an issue and you're confident your users will enable macros etc then I would go for UDF -- I say that only because morefunc.xll is fairly significant piece of kit if you intend to use only one function...

I would suggest perhaps having a read through of the other functions available via morefunc.xll and see if some of them can be put to use to replace other more complex native functions - if so morefunc.xll may be a worthwhile venture...

The other morefunc functions seen most frequently on this board would be things like:

• MCONCAT (v.useful for concatenating large volume of values)
• EVAL
• INDIRECT.EXT (like INDIRECT but works with closed targets)
• THREED (v.useful for 3-D conditional summation)
• ARRAY.JOIN (v.useful for creating contiguous ranges out of non-contiguous ranges)
• NBTEXT

[DonkeyOte]

In native formula terms this I think might work but it's a Volatile Array and as such a very poor performer in terms of efficiency:

Please Login or Register  to view this content.

[Robo]

Sorry, I was called off site.

Thanks very much!!!

I couldnt see a problem with it, it seems to work.

Kind regards

Robo

[Robo]

Hi Again

Is there any way to get it to report out the first 5 digits where five exist, but still only report 4 if only 4 exist?

Regards

Rob

[DonkeyOte]

With a Regular Expression based approach yes it's quite easy...

Using morefunc.xll:

=REGEX.MID(C2,"([0-9]{4,5})")

Using the UDF alter the pattern as per above:

Please Login or Register  to view this content.

I would advocate either of the above approaches in preference to native Volatile Array functions...

[Robo]

Hi

What would the old array approach be? i have tried to manipulate your formula, but failed. I would rather do it that way if possible.

Rob

[daddylonglegs]

Try this formula in H2 copied down

=LOOKUP("zzzzzz",CHOOSE({1,2},TEXT(LOOKUP(10^4,MID(SUBSTITUTE(SUBSTITUTE(C2," ","x"),"/","x"),ROW(INDIRECT("1:"&LEN(C2)-3)),4)+0),"0000"),TEXT(LOOKUP(10^5,MID(SUBSTITUTE(SUBSTITUTE(C2," ","x"),"/","x"),ROW(INDIRECT("1:"&LEN(C2)-4)),5)+0),"00000")))

[Robo]

Hi

Ive tried it and it works. Thanks very much!!!

Regards

A happy Rob