+ Reply to Thread
Results 1 to 5 of 5

Need VBA to Extract data from Column A to Column B

  1. #1
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Need VBA to Extract data from Column A to Column B

    I have in a column various text arrays. In those arrays you will find E-Numbers like E270. Those E-Numbers I want to have in the column next to it separated. Can you create an Excel formula for this?

    Example text in one cell:
    Rapsöl, Ziegen-/ KuhMilch-Frischkäsezubereitung 29% (ZiegenMilch[Niederlande], Kuhmilch [Deutschland], Kochsalz, Olivenöl, Wasser, Säuerungsmittel [E 270], modifizierte Maisstärke [E 1422], Pfeffer, Knoblauch), Paprikaschoten, Kochsalz, Säuerungsmittel (E 330, E 260), Festigungsmittel (E 509).


    This Data is in Cell A1:
    Rapsöl, Ziegen-/ KuhMilch-Frischkäsezubereitung 29% (ZiegenMilch[Niederlande], Kuhmilch [Deutschland], Kochsalz, Olivenöl, Wasser, Säuerungsmittel [E 270], modifizierte Maisstärke [E 1422], Pfeffer, Knoblauch), Paprikaschoten, Kochsalz, Säuerungsmittel (E 330, E 260), Festigungsmittel (E 509).

    What I Need:
    I need to Extract all E number to cell B1 like you can see on Cell A1 E 270, E 1422, E 330, E 260, E509

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need VBA to Extract data from Column A to Column B

    Hi,

    Here's one UDF that should work.

    Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Please Login or Register  to view this content.
    Last edited by Richard Buttrey; 02-06-2016 at 04:41 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Need VBA to Extract data from Column A to Column B

    Also a UDF

    Use in cell like
    =JoinWithChr(A1,"E",", ")

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Need VBA to Extract data from Column A to Column B

    Thanks, VBA working perfectly.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Need VBA to Extract data from Column A to Column B

    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.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Extract related data from a second column from repeated entries in a first column
    By pachorradas in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-31-2015, 02:41 PM
  2. Extract data from one column & place in adjacent column
    By Heterodoxy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-27-2014, 12:50 PM
  3. Delimiter-like function to extract data from a column and put in another column
    By MikeStewart1 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-03-2014, 02:42 PM
  4. Replies: 2
    Last Post: 10-31-2013, 01:20 AM
  5. [SOLVED] Extract first cell in a row, given the column header and data point in that column.
    By pguarino in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2013, 02:27 AM
  6. Replies: 1
    Last Post: 04-23-2012, 10:27 AM
  7. Replies: 3
    Last Post: 02-08-2010, 06:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1