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

**ClaudiaRovastino** · 10-06-2021, 06:35 PM

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

**TMS** · 10-06-2021, 07:52 PM

Something like:

Formula:

=MID(INDEX(A:A,MATCH("Client Number"&"*",A:A,0)),LEN("Client Number")+1,99)

Formula:

=MID(INDEX(A:A,MATCH("Client Name"&"*",A:A,0)),LEN("Client Name")+1,99)

etc

**ClaudiaRovastino** · 10-09-2021, 11:50 AM

Works great. Thank you so much !!!

**Croweater** · 10-06-2021, 08:50 PM

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?

**bsalv** · 10-06-2021, 09:20 PM

depending on the answers on Croweater's questions, it can even be better than this (th 5 items on 1 row)

Sub Extraction()

     searchtext = Array("Client Number", "Client Name", "Type of Business", "Region", "Phone Number")
     Set dict = CreateObject("scripting.dictionary")

     With Sheets("sheet1")
          arr = .UsedRange.Columns(1)
          For i = 1 To UBound(arr)
               For Each st In searchtext
                    If Left(arr(i, 1), Len(st)) = st Then dict.Add dict.Count, Array("'" & Trim(Mid(arr(i, 1), Len(st) + 1)), 1): Exit For
               Next
          Next

          .Range("B2").Resize(dict.Count).Value = Application.Index(dict.items, 0, 1)
     End With
End Sub

**jindon** · 10-07-2021, 12:03 AM

Originally Posted by ClaudiaRovastino

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

Sub test()
    Dim e
    With Columns(2)
        .Columns(0).Copy .Cells
        For Each e In Array("Number", "Name", "Business", "Region")
            .Replace "*" & e, "", 2
        Next
        .Resize(.Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(4).Delete xlShiftUp
    End With
End Sub

**alansidman** · 10-09-2021, 11:56 AM

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.

**TMS** · 10-09-2021, 01:06 PM

You're welcome. Thanks for the rep.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Match part of cell from another column, return corresponding text

[SOLVED] Code to search Excel and return results even if part of search text is present

Search a column for 2 conditions and display the cell content of last match

Need to find text in a named range, and return part of the text to a single cell

[SOLVED] search text of cell in a range and return the contents in the corresponding column

[SOLVED] Search Column for text, Return Cell below to another sheet?

[SOLVED] Excel 2007 : If a cell contains this part text, or this part text, etc.., return this value, or nothing

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions