+ Reply to Thread
Results 1 to 8 of 8

Search a column for parts of text and return another part of text within the same cell

  1. #1
    Registered User
    Join Date
    10-31-2019
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    19

    Search a column for parts of text and return another part of text within the same cell

    Hello friends,

    I have a very long list in column A that looks like the example bellow. The actual list is long and random and has many spaces between the cells that contain information.

    Example:

    A25 = Client Number123456789
    A36 = Client NameMickeyMouse
    A45 = Type of BusinessBarber Shop
    A49 = RegionEast
    A56 = Phone Number123-456-7890

    I have been trying to find a way to automatically search let's say Client Number and return his number 123456789 and place it in B1
    Search Client Name and return MickeyMouse and place it (MickeyMouse) in B2
    and so on...

    Result wanted:
    B1 = 123456789
    B2 = MickeyMouse
    B3 = Barber Shop
    B4 = East
    B5 = 123-456-7890

    Could you please help me with a VBA Code that can do this.

    Thanks a lot in advance
    Claudia

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,762

    Re: Search a column for parts of text and return another part of text within the same cell

    Something like:

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

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


    etc

  3. #3
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    782

    Re: Search a column for parts of text and return another part of text within the same cell

    If the solution offered satisfies your requirement then I'll bow out. If not I'm happy to help with some VBA code.

    A few questions.

    1. You say the list is long and 'random'. Is all the information for one client lumped together?
    In other words, can I be sure that if I find a client number in the list, then the very next phone number (for example) belongs to that client?

    2. Is the 'order' consistent (ie. does Client name ALWAYS follow the client number, albeit with some other stuff in between that you are not interested in)?

    3. Are those 5 things the only information that exist for each client or the only 5 things you want to store in column B?

    4. Can you mock up a small example so that we can see the type of 'randomness' that exists, what you expect from the VBA code and attach it to your post?

  4. #4
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,014

    Re: Search a column for parts of text and return another part of text within the same cell

    depending on the answers on Croweater's questions, it can even be better than this (th 5 items on 1 row)
    Please Login or Register  to view this content.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    Re: Search a column for parts of text and return another part of text within the same cell

    Quote Originally Posted by ClaudiaRovastino View Post
    Example:

    A25 = Client Number123456789
    A36 = Client NameMickeyMouse
    A45 = Type of BusinessBarber Shop
    A49 = RegionEast
    A56 = Phone Number123-456-7890

    Result wanted:
    B1 = 123456789
    B2 = MickeyMouse
    B3 = Barber Shop
    B4 = East
    B5 = 123-456-7890
    try
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-31-2019
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    19

    Re: Search a column for parts of text and return another part of text within the same cell

    Works great. Thank you so much !!!

  7. #7
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,702

    Re: Search a column for parts of text and return another part of text within the same cell

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,762

    Re: Search a column for parts of text and return another part of text within the same cell

    You're welcome. Thanks for the rep.

+ 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. Match part of cell from another column, return corresponding text
    By hibee_easty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2019, 11:10 AM
  2. [SOLVED] Code to search Excel and return results even if part of search text is present
    By Taoyuan00 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2016, 09:20 AM
  3. Search a column for 2 conditions and display the cell content of last match
    By ansridhar in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-17-2014, 06:41 AM
  4. Replies: 7
    Last Post: 01-24-2013, 06:55 PM
  5. [SOLVED] search text of cell in a range and return the contents in the corresponding column
    By kumail in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-18-2013, 06:42 PM
  6. [SOLVED] Search Column for text, Return Cell below to another sheet?
    By Dewy1425 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-01-2012, 08:07 PM
  7. Replies: 5
    Last Post: 04-08-2012, 01:06 AM

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