Results 1 to 8 of 8

Thread: VBA: Extracting multiple strings from cells onto multiple columns.

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Extracting multiple strings from cells onto multiple columns.

    Greetings,



    Long time lurker, first time poster. I'm not sure if this is possible in VBA (I searched but didn't find anything close to my situation), but I have a spreadsheet containing invoice information in column A. In each cell, there are PKS numbers, which I'm trying to extract onto the columns directly to the right. Some cells contain multiple PKS numbers, which is why I'm not sure how this could be done.






    This is the final result I'm trying to achieve.






    -JM

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,647
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA: Extracting multiple strings from cells onto multiple columns.

    JMudd,

    Welcome to the MrExcel forum.

    We can not tell what worksheet(s), cells, rows, columns, your raw data is in.

    And, we can not tell what the results should look like.

    Can you post a screen shot of what your data looks like?

    Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-...forum-use.html


    Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com.

    Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

    Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets.

    If the workbook contains confidential information, you could replace it with generic data.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  3. #3
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Extracting multiple strings from cells onto multiple columns.

    Image links in yr post are broken, sorry

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,647
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA: Extracting multiple strings from cells onto multiple columns.

    Quote Originally Posted by sergioMabres View Post
    Image links in yr post are broken, sorry
    Thanks for that.


    Try the following link, reply #2:

    https://www.mrexcel.com/forum/about-...tachments.html
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Extracting multiple strings from cells onto multiple columns.

    Here's a link to the file
    https://www.dropbox.com/s/v5fi87k036...late.xlsx?dl=0

    Column A contains the cells with data and I would like to extract the "PKS" numbers onto each following column

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA: Extracting multiple strings from cells onto multiple columns.

    How about
    Code:
    Sub JMudd()
       Dim Cl As Range
       Dim Sp As Variant
       Dim i As Long, j As Long
       
       For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
          Sp = Split(Cl, Chr(10))
          For i = 0 To UBound(Sp)
             If InStr(1, Sp(i), "PKS", vbTextCompare) > 0 Then
                j = j + 1
                Cl.Offset(, j).Value = Split(Sp(i))(0)
             End If
          Next i
          j = 0
       Next Cl
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Extracting multiple strings from cells onto multiple columns.

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub JMudd()
       Dim Cl As Range
       Dim Sp As Variant
       Dim i As Long, j As Long
       
       For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
          Sp = Split(Cl, Chr(10))
          For i = 0 To UBound(Sp)
             If InStr(1, Sp(i), "PKS", vbTextCompare) > 0 Then
                j = j + 1
                Cl.Offset(, j).Value = Split(Sp(i))(0)
             End If
          Next i
          j = 0
       Next Cl
    End Sub
    This works perfectly!!! Thank you so much Fluff!!!

    -JM

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA: Extracting multiple strings from cells onto multiple columns.

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •