Extract Number with Prefix from Text

[myou]

Hello dear Forum Members,

I am trying to extract All numbers with a Prefix of "E" in a Column. All numbers will always be E12345 or E and 5 numbers. I got it to work somehow as shown in the attached sample Workbook, but there appears extracted numbers which are not correct.

Would anybody be able to assist me with this?

Thank you!

[Phuocam]

Try this:

Please Login or Register  to view this content.

[romperstomper]

You could also use RegEx:

Please Login or Register  to view this content.

[myou]

@ Phuocam,

Thank you for your time and help. Unfortunately your Function does not include the "e" with the Extracted number, which is what I ideally want.

Do you perhaps have a workaround for this, and have the Code not to be Case Sensitive regarding the "E"?

Many thanks!

[myou]

@ rorya,

Many thanks for your time and help as well. Your Code is Case Sensitive which I would like to avoid. Your Function also ignores any number which is followed with Text without a space after the number.

Sorry to bother you guys with these issues, but I would really appreciate it if you could solve these few glitches.

[romperstomper]

Change the function to:

Please Login or Register  to view this content.

[jindon]

If you don't want to extract E with more than 5 numbers then try

Please Login or Register  to view this content.

[myou]

@ rorya,

JACKPOT!!! Absolutely awesome! Kudos to you. That is brilliant!

Thank you for your time and help, I appreciate it.
Rep added!

[romperstomper]

Glad to help, and thanks for the rep.

I'd recommend looking at the pattern in Jindon's version if you might have Ennnnn as part of another number that should be ignored.

[Phuocam]

Try the following code:

Please Login or Register  to view this content.

[myou]

Thank you jindon!

[jindon]

You are welcome and thanks for the rep.

Don't forget to mark the thread as solved...

[myou]

Hello Guys,

My original requirement for looking at 5 Numbers only, has changed to Any number of integers after a prefix which is selected from a Drop Down Cell. I wish to also include the number of integers to look for via a Drop Down Cell in Cell L1.

Please see the attached sample Workbook which better shows my requirement.

Thanks!

[jindon]

1) Replace "Function GetNumWithPref" with

Please Login or Register  to view this content.

2) Replace below one line in "Sub ExpNumbers()"

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.