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

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?

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

Please Login or Register  to view this content.

Works great. Thank you so much !!!