Results 1 to 3 of 3

Thread: formula/vba help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default formula/vba help

    hi friends,
    I have the following txt strings that are not delimited and I would like to have the data separated

    0120190624INIT 99999
    0312345678920190621AB 000000000000000000 000000000000000000122365ABC
    0310111213120190621AB 000000000000000000 0000000000000000001143ABC
    0500002


    the logic is as follows:

    in the string:

    first 2 digits 01 is the header row. if 05 it is the footer row.
    if the first two digits are 03 denote that this is the row to be analyzed.
    the next 9 digits after the 03 (i.e 123456789) denote an identifier that needs to be separated
    the next 8 digits denote the date (20190621) this also needs to be separated

    in the end of the string is the value I need after the last zero and having the numbers and letters separated (122365ABC)

    the end result I want is below:

    123456789 06/21/2019 122365 ABC

    not sure what is the easiest way to accomplish this, whether formulas or vba.
    All the lengths of characters that need to be extracted from the string are fixed as well as the format.

    please help. TIA

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,813
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: formula/vba help

    in the end of the string is the value I need after the last zero and having the numbers and letters separated (122365ABC)
    Is it of the fixed width format, so these values you need at the end of the string ALWAYS start in the same place (the 60th character space)?
    Do you want the original data overwritten, or do you want what needs to be extracted put on a separate sheet?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,813
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: formula/vba help

    If my assumption is correct, and assuming that your data begins in cell A1 and goes down column A, you could get the values you want listed out in columns B, C, and D via simple worksheet formulas like this:
    in cell B1: =IF(LEFT($A1,2)="03",MID($A1,3,9),"")
    in cell C1: =IF(LEFT($A1,2)="03",MID($A1,12,8),"")
    in cell D1: =IF(LEFT($A1,2)="03",MID($A1,60,LEN($A1)),"")
    and then copy down for all rows.

    If you want some sort of VBA solution, please answer the question regarding where you want these results to go.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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