+ Reply to Thread
Results 1 to 7 of 7

Search cells for a string and when that string exists copy entire cell to another cell

  1. #1
    Registered User
    Join Date
    06-04-2015
    Location
    houston, tx
    MS-Off Ver
    2010
    Posts
    3

    Search cells for a string and when that string exists copy entire cell to another cell

    I have a spreadsheet where I need all the data of one type in its own column.
    Currently the data appears randomly in any column.
    The data literally has (D), (B), (A), (L) next to it in each cell like this:

    ColA ColB ColC ColD
    (D)30 (B)10 (L)100 (A)45
    (A)6 (D)21 (B)92 (L)2
    (L)3 (A)64 (B)9 (D)65

    So what I need to do is take all of the contents of each cell and line it up like this within the same row the data is found in like this:

    ColE ColF ColG ColH
    (A)45 (B)10 (D)30 (L)100
    (A)6 (B)92 (D)21 (L)2
    (A)64 (B)9 (D)65 (L)3

    I have tried all sorts of mid, find combinations and I cant get it to work right.

    Thank you in advance for any help!

    Edit to add example file
    http://magicmolder.com/sample.xls
    What I am trying to achieve starts in column O and goes to the right.
    Last edited by nitro535; 06-04-2015 at 04:21 PM. Reason: add example file

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Search cells for a string and when that string exists copy entire cell to another cell

    you get better help on your question if you add a small example of your file, without confidential inforation.

    Please also add the expected result manualy in your file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-04-2015
    Location
    houston, tx
    MS-Off Ver
    2010
    Posts
    3

    Re: Search cells for a string and when that string exists copy entire cell to another cell

    I added a sample file with an example of what I am trying to achieve.
    Any ideas?

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Search cells for a string and when that string exists copy entire cell to another cell

    Here is one possible solution. I created a range identical in size to the original to the right of the original range. Every time that B A D R d is encountered a number from 1 to 5 is entered in the helper range in the same relative position in the range as the original (hope that makes sense). This is the formula used. Fill across and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The helper range was converted back to original cell contents in sorted order using this formula filled across and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The result is the orange coloured range in the file enclosed:
    NOTE: There were many extraneous spaces in nearly if not all cells of the original data. I used the TRIM function to remove those spaces.

    I chose an area large enough to accommodate the data under the original data and entered this formula to trim the spaces from the original data. Fill across and down. After trimming, select and copy the trimmed data and Paste Values back in place to get rid of the formulae then delete the original data and move the new data into the original data position:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by newdoverman; 06-04-2015 at 06:22 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    06-04-2015
    Location
    houston, tx
    MS-Off Ver
    2010
    Posts
    3

    Re: Search cells for a string and when that string exists copy entire cell to another cell

    Thank you sir,
    Very much appreciated!

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Search cells for a string and when that string exists copy entire cell to another cell

    You're welcome.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Search cells for a string and when that string exists copy entire cell to another cell

    I have an update to the first formula that I gave you. It takes care of the possibility that a lower case d could appear as the second character in a cell but not have an opening parenthesis.

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


    It is unlikely that an upper case D will be the second character in a cell so I did nothing about that.

+ 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. Search Column A & C for string and copy entire row to new sheet
    By ludsonline in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2014, 02:43 AM
  2. Search a cell for a string that exists in a list of values
    By jefflach in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2013, 11:23 AM
  3. Replies: 7
    Last Post: 01-09-2013, 02:28 PM
  4. Identify if string exists, copy entire row.
    By JapanDave in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-26-2011, 02:57 AM
  5. Search Selected Data for a Text String and replace entire cell with new tex
    By maz003 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2010, 02:26 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