1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel - Find data, Copy to new cell

Discussion in 'Business Applications' started by daviesma, Mar 16, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. daviesma

    daviesma Guest Thread Starter

    Joined:
    Mar 16, 2010
    Messages:
    13
    I have an Excel 2007 spreadsheet with 9,000+ entries. Column F contains text data (50-60 characters), which will include a 10 character serial number in the format "xxnnnnnnnn". The first two characters will always be alpha and the next eight will always be numeric.

    I need to find each of these 10 character strings and copy them to column G of the same row. I do not want to do this manually if at all possible. Is there an excel formula that can do this type of function?

    Your assistance is very much appreciated.

    Mark
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,988
    First Name:
    Wayne
    will the serial number be in a specific place in the string or can it be anywhere - if always in a specific position you could use the MID function

    can you post some examples of the character string and the serial number - so we can see the text and the format of the serial number within the text
    what are the chances of the 50-60 characters having multiple random strings that just happens to also have 2 alpha and 8 numeric
     
  3. daviesma

    daviesma Guest Thread Starter

    Joined:
    Mar 16, 2010
    Messages:
    13
    Below is an example of a text string (not including the parens) :

    "AKRON, OH,44307~AKRNOHAH~01.DCO23.00~FW04080464~00095844~ENS-DATA CENTER SUPPORT~"

    The serial number in this string is "FW04080464".

    Mark
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,988
    First Name:
    Wayne
    So will it always be in that position of the string - or always preceded by 00~ or any unique set of texts
     
  5. daviesma

    daviesma Guest Thread Starter

    Joined:
    Mar 16, 2010
    Messages:
    13
    Sorry, it will not always in same position, since a previous field may not be entered. The only thing that is certain is that it will have the format ~xxnnnnnnnn~ and will usually be found near the middle.
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    would this always be unique:
    ~xxnn
    as a start of the data?
    Also, are the "xx" restricted to certain combinations?
     
  7. daviesma

    daviesma Guest Thread Starter

    Joined:
    Mar 16, 2010
    Messages:
    13
    No restrictions on "xx" - they can be any character of the alphabet and most all will be used.

    the string will always start with "~xxnnnnnnnn~". All letters and numbers will be used over time.
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Will the "~xxnnnnnnnn~" always be preceded by numeric data, such as "01.DCO23.00" in the string above?
     
  9. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Can you attach a sample workbook (remove any sensitive data with dummy data)? Just create a sampel workbook that shows how the workbook looks currently, and what you want it to look like when completed.
     
  10. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Are these different cells within the worksheet, or is all of this data in the same cell?
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    "Column F contains text data (50-60 characters), which will include a 10 character serial number in the format "xxnnnnnnnn"" so all the data is in the same cell.
    And Text to Columns would be messy, at best, for "...it (note: the data to be extracted) will not always in same position, since a previous field may not be entered." :(
     
  12. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Yeah, I n oticed that once I went back and read the problem again. I am in the process of trying to figure something out. I guess the questions is did the OP place the ~ in the port just to separate the data or if the ~ is actually in the cell.
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I get the feeling it is coming like this - not something the poster has control over. One could use the tildes for Text to Column breaking, but since some fields may or may not exist - and the length will also vary - that will just be a mess.
     
  14. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Slurpee, I agree. I was just pondering if the ~ does exist within the cell, then that could be a possible helpful character.
     
  15. daviesma

    daviesma Guest Thread Starter

    Joined:
    Mar 16, 2010
    Messages:
    13
    The ~ preceeds all entries of the data. eg, "~xxnnnnnnnn".
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/910555

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice