formula for separate number from Text

what is your expected result first E270 or all Numbers with E
then is your data will be in same setup as it is shown here

Assuming text is in A1:

Please Login or Register  to view this content.

Drag across till getting "E0"

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

First of all bebo's formula works well
another approach may be copy paste below in B1 then hold control and shift together and then hit enter to make it array formula and drag to right

Formula:

Please Login or Register  to view this content.

Third to extract all in column B1 you need to use VBA




Array formulas are...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Hi Farhan,

Following on from your PM request and my suggestions, glad to see you got some great help here.
You were able to explain your problem much better!

It would be courteous to put a link to similar Threads you are posting
http://www.excelforum.com/excel-prog...-column-b.html
http://www.excelforum.com/excel-form...from-text.html
That way the Helpers are less likely to Duplicate work.

( Note also please for future reference and read please Forum Rules
http://www.excelforum.com/forum-rule...rum-rules.html
especially please read for future reference:
Rule 4. Don't Private Message or email Excel questions to moderators or other members.
And
Rule 5. Don't duplicate threads )
( and Rule 1. 1. Use concise, accurate thread titles – you made a big improvement there! )

_ .....................................................
But no harm done this time as you are still very new here, : Glad you got some great help with many alternatives. It made it all great to learn in seeing the alternatives from so many people!!

BTW. Note again what both bebo021999 and hemesh said
‚……Array formulas are...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer….“

Their formulas all worked great! ( I have implemented them for you in the enclosed File, putting those CSE Type 1 formulas in manually - ), ( - The only difficulty i am still investigating is that I was unable to use VBA to put those formulas in using .FormulaArray = ***** )

Hope that is all a good contribution
Alan

Enclosed: ( Attached ) A Summary File I used while learning from these two posts
See Sheet “farhangulNährwerteE“ and Sheet Code Module for the CSE Formulas implementation and my ( failed ***** ) attempt at putting them in with code )
And Normal Macro Module: „PubsUDFs“ for the 3 given Functions
( I will update this file as I learn and understand more of it, and in particular RegEx stuff... )
File “BrentacvegasjindonRegexFahan.xlsm”
https://app.box.com/s/5wgwb14vv6thu5v02douh9r2nsglnohf




Referrences
_a) http://www.excelforum.com/excel-new-...ml#post4290062
_b ) http://stackoverflow.com/questions/2...cell-and-loops

_c) I liked this, sorry i lost the reference
“.......If you can solve your problem without regular expressions (just by string functions), you don't use regular expressions. As it was said in one book I've read: regular expressions are violence over computer....”.
_d) Formula translator
http://www.excel-ticker.de/excel-formel-uebersetzer/
_e) Misc...
http://excelmatters.com/2015/03/17/on-error-wtf/
https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo


Thanks all again, great learning experience along with the other alternatives.
Alan.

And as last.... here is another Function ( and test calling code ) along the line of jindon’s
Copy all Code to a Normal Macro Code Module
Type in a cell
=ENummern(A1) ( or =ENummern(A35).... etc ) where Cell A1 ( or Cell A35) has your string of ingredients in it enit )

Code:

Please Login or Register  to view this content.