+ Reply to Thread
Results 1 to 9 of 9

A vba code that extracts a specific text along with the next 8 digit number that follows

  1. #1
    Registered User
    Join Date
    01-13-2018
    Location
    Ethiopia
    MS-Off Ver
    2013
    Posts
    27

    A vba code that extracts a specific text along with the next 8 digit number that follows

    Hello Everyone,

    i really need help with the following..

    A vba code that extracts a specific text along with the next 8 digit number that follows.

    Only three scenarios are available:

    Scenario 1: PSI2164610ETHE70162015ed366749hhe

    In the first scenario I need excel to only select the string “ETHE” and the 8 digit number that follows which is 70162015 and display in the next column as ETHE70162015. Therefore, a specific text and the 8 digit column that follows is selected and displayed.

    Scenario 2: PSI2164610BED70162015ed366749hhe

    In the second scenario I need excel to only select the string “BED” and the 8 digit number that follows which is 70162015 and display in the next column as BED70162015. Therefore, a specific text and the 8 digit column that follows is selected and displayed.

    Scenario 3: PSI2164610HRA70162015ed366749hhe

    In the third scenario I need excel to only select the string “HRA” and the 8 digit number that follows which is 70162015 and display in the next column as HRA70162015. Therefore, a specific text and the 8 digit column that follows is selected and displayed.

    THANK YOU SO MUCH FOR THE HELP!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    Re: A vba code that extracts a specific text along with the next 8 digit number that follo

    Posted in The Water Cooler (wrong) - now moved to the VBA section (right).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: A vba code that extracts a specific text along with the next 8 digit number that follo

    Try this, if you have this uniform list.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

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

    Re: A vba code that extracts a specific text along with the next 8 digit number that follo

    UDF
    =GetNumbers(A1,8)
    Where A1 = cell that you have orignal string, 8 = number of consecutive digits
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-13-2018
    Location
    Ethiopia
    MS-Off Ver
    2013
    Posts
    27

    Re: A vba code that extracts a specific text along with the next 8 digit number that follo

    Hi Jindon,

    your code is absolutely brilliant!! thank you so much.

    Only two problems with your code:

    1st In the case where the number is like this: "ETHE003420171 PSI21811623", The formula took PSI21811623 instead of ETHE00342017 which as you can see is highly unrelated

    2nd In the case where the number is like this: "ETHE003420171", the formula returned nothing (Empty) when I need it to extract ETHE and the first 8 digit number and ignore the 9th digit which should become ETHE00342017 in which case the ninth digit which is “1” is ignored because all I need is the word ETHE and the first 8 digits next to the ETHE.

    Thank you

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

    Re: A vba code that extracts a specific text along with the next 8 digit number that follo

    Do you mean
    Please Login or Register  to view this content.
    ?

  7. #7
    Registered User
    Join Date
    01-13-2018
    Location
    Ethiopia
    MS-Off Ver
    2013
    Posts
    27

    Re: A vba code that extracts a specific text along with the next 8 digit number that follo

    Modified the code like this:

    Please Login or Register  to view this content.

    but the problem still exists.. i dont know why.. but i think you are almost there sir
    Last edited by ermias; 07-04-2018 at 08:44 AM.

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

    Re: A vba code that extracts a specific text along with the next 8 digit number that follo

    1)Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    2) You are not following the change I made.
    Please Login or Register  to view this content.
    not
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-13-2018
    Location
    Ethiopia
    MS-Off Ver
    2013
    Posts
    27

    Re: A vba code that extracts a specific text along with the next 8 digit number that follo

    Thank you so much! It works like charm!

    Genius!!!

+ 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. Random generating a 10 digit number that only contains specific digits.
    By mutzie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-03-2017, 09:46 AM
  2. Replies: 20
    Last Post: 05-10-2016, 06:05 PM
  3. Code that extracts cell contents from specific cells
    By TreeLife in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-24-2016, 08:43 AM
  4. [SOLVED] Code that extracts specific sheet in multiple workbooks to one workbook
    By KT999 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-04-2014, 10:05 AM
  5. [SOLVED] how to add specific number in a digit.
    By Muhammad Shafique in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-09-2014, 12:39 PM
  6. Replies: 3
    Last Post: 04-24-2012, 09:16 AM
  7. Replies: 2
    Last Post: 06-17-2010, 08:36 PM

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