+ Reply to Thread
Results 1 to 12 of 12

Copy text to next cells based on numbers between #A and #B

  1. #1
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Copy text to next cells based on numbers between #A and #B

    There is random multi-line text (as below example in one cell)min most of the cells of column B of the excel sheet.

    #A [G2]
    2G 1A -11.2 2.361 G(1)
    #B
    00000000
    00000000
    #A [G2]
    2G 1A -11.2 2.361 G(8a)
    #B
    #A [G2]
    1G 1A -11.2 2.361 G(8)
    #B
    00000000
    00000000
    #A [G2]
    3G 1A -11.2 2.61 G(7)
    #B

    #A [G2]
    3G 1A -1.2 2.361 G(6)
    #B
    #A [G2]
    1G 1A -11.2 2.1 G(8c)
    #B
    00000000
    00000000

    I need the output as below as
    in column C cells: G(8), G(8c)
    in column D cells: G(1), G(8a)
    in column E cells: G(7), G(6)
    Attached Files Attached Files
    Last edited by zeo1; 12-31-2021 at 06:24 AM. Reason: Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Copy text to next cells based on numbers between #A and #B

    I feel as if we are missing some information, however to replicate the results in columns C:E on the output sheet:
    For C2 and down: =IF(B2<>"","G(8), G(8c","")
    For D2 and down: =IF(B2<>"","G(1), G(8a)","")
    For E2 and down: =IF(B2<>"","G(7), G(6)","")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

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

    Re: Copy text to next cells based on numbers between #A and #B

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: Copy text to next cells based on numbers between #A and #B

    G(8), G(8c), G(1), G(8a), G(7), G(6) are example values.
    They are in different combinations in original data.
    For example, if it's appAB(A1A2) then the code won't work for me.
    Help.

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

    Re: Copy text to next cells based on numbers between #A and #B

    Then upload more realistic data.

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Copy text to next cells based on numbers between #A and #B

    @zeo1... Here is something I have posted in the past which may be of help in the future:

    One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more.

  7. #7
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: Copy text to next cells based on numbers between #A and #B

    My apologies.
    I have updated the excel file.

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

    Re: Copy text to next cells based on numbers between #A and #B

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 12-31-2021 at 07:39 AM.

  9. #9
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: Copy text to next cells based on numbers between #A and #B

    Thank you but
    .Value = a turns into yellow for some cell data.

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

    Re: Copy text to next cells based on numbers between #A and #B

    If you are talking about the data that I don't know, everything might happen.
    Upload the workbook that has such problem.

  11. #11
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: Copy text to next cells based on numbers between #A and #B

    Sir, If I can delete all the multi-line sentences (P.S. some extra random spaces appear before them and also between the words) then the VBA code works great.
    Last edited by zeo1; 01-05-2022 at 07:32 PM.

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

    Re: Copy text to next cells based on numbers between #A and #B

    It should work even if blank line(e) anywhere.

+ 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. Replies: 6
    Last Post: 12-28-2021, 12:04 PM
  2. Copy cells based of certain text or blanks spaces?
    By Jonathanbarrick in forum Excel General
    Replies: 3
    Last Post: 07-10-2020, 10:59 PM
  3. [SOLVED] Looking for a way to sort a range of cells that are text based that contain numbers.
    By scam in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-29-2020, 06:59 AM
  4. [SOLVED] Auto populate sequential ID numbers based on any text entered into adjacent cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2017, 11:41 PM
  5. Copy lines of text cells, to another area, based on color of text.
    By richard11153 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-10-2016, 03:53 PM
  6. [SOLVED] Can you formulate cells for text based on a cell with numbers?
    By mainbrain in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2015, 10:35 PM
  7. [SOLVED] Change text of one cell based on the numbers of a range of cells?
    By spookymyo in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-18-2013, 04:50 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