Finding certain word in cell....then split text

**Lynx2x** · 02-21-2012, 07:13 AM

Hello!

As you can see I'm new here and I hope I wont do anything wrong posting this thread.

So my problem is like this.
In column A, I have names, surnames, middlename (if there is) and also in some cases some other informations, like S.P., D.O.O.,MAG.,DR.,....(I will call it "other info")
First of all I want to delete that "other info" (S.P.,D.O.O,...) from every cell where it is written. If it is possbile I would like to have these "other info" written in sheet2 and program would lookup if lets say cells A1:A100 have any of these "other info" in it and if there is it would delete it (not the whole cell, just "other info"). Why written in sheet2 - because if I realize that there is some new word I dont need it, i will simply add it onto the list.
When this is done, I would like so separate names and surnames, (middlenames) each in own column.
I know for find and replace and also for text to columns, but I would like to have a code in macro, so I could do that with a simple selection and click.

Any thoughts?

Thanks for all answers in advance and sorry if my Englih is not the best.

Sencirely, Tilen

**StephenR** · 02-21-2012, 07:19 AM

Tilen - I suggest you post a workbook with a decent sample of data and your desired results. How will you define what "other info" covers?

**Lynx2x** · 02-21-2012, 08:15 AM

Hello again!

I've attached my workbook and in it there are 50 entries under column A (sheet1 - DATA). In sheet2 (Other Info) there are words that can be found in some cells in column A(sheet1 - DATA). I was thinking some LookUp would do the trick, but I dont know how to do a code that would lookup if in column A there is any of those words in any cell and if there is that would delete it.
After that, I want to separate Data from column A to columns B, C, D.

At the end it should be something like this:

A B C D

Tilen Mirt | Tilen | Mirt
Tilen Lucija Bogolin | Tilen | Lucija | Bogolin

Hope you understand what I am looking for.

Thank you very much.

Tilen

Example.xlsx

**StephenR** · 02-21-2012, 08:34 AM

OK, try this. I avoided TTC in the end because of the variability in the names:

Please Login or Register  to view this content.

**Lynx2x** · 02-21-2012, 08:55 AM

Oh men....thank you thousand times!!!

That code does exactly what I was looking for! Thank you very much I really appreciate your help.

I have just one more question.

What if, in column A(DATA) , there are more than three word, lets say something like this: Tilen Lucija Mirt Bogolin
I've noticed that in that case programe puts everything together in column B.
Is there a possibility that in cases like that or in cases with even more words it would automaticly put in next columns (E, F, G,...).

And I am sorry but I dont know what you mean with TCC?

Everything else is working perfectly and I am happy I can not describe

Tilen

**bebo021999** · 02-21-2012, 09:07 AM

Alternative way by formula.
Firstly, you must write down all possible "other info" in "Other info" sheet, to make "other info" bank. This list can be updated frequently.
Assuming that only 1 "other info" occured in name.
In B2, name without "other info"

Please Login or Register  to view this content.

Confirmed with both press Ctrl and shift, then enter
With name "POS"

Please Login or Register  to view this content.

name "List"

Please Login or Register  to view this content.

C2, middle name:

Please Login or Register  to view this content.

D2, surname:

Please Login or Register  to view this content.

I am not sure about surname or middle name, so try to change together in it wrongs.
Hope it helps

**StephenR** · 02-21-2012, 09:09 AM

Maybe this, but where should the surname go if only two names?

Please Login or Register  to view this content.

If you just want them to go from left to right you could replace the If statement with

Please Login or Register  to view this content.

**Lynx2x** · 02-21-2012, 09:56 AM

Ok, I've tried both of codes but everytime I get error and everything is just copied from column A to B.

Mybe I wasn't clear enough befor. What I ment, lets say we dont know what is name and what is surname or middlename and after we clean "Other Info", we just want to separate the text from column A to B,C,D,E,...depending on how many words there are in column A.

Hope you know what I mean.

I've also seen your reply, bebo021999, and I thank you for that.

Thank you both.

**StephenR** · 02-21-2012, 10:17 AM

That's what the second bit of code does, so you need all this:

Please Login or Register  to view this content.

**Lynx2x** · 02-21-2012, 10:23 AM

Works perfectly!

Thank you very much again. Both of the codes (if we know Name, Surname,...and if we dont know) will bi very helpful for me.

Have a nice day!

Tilen Mirt

**Lynx2x** · 02-22-2012, 04:50 AM

Good morning to everyone!

Yesterday you have helped me with code above, and today I've tried it one more time, but on bigger database with 30.000 records in it. Everything is okay for first 3k of records but then I get an error in vba that it says: Run-time error'1004': Application - defined or object-defined error.
When I press debug I get yellow colored line: r.Resize(, UBound(v) + 1) = v

I would really appreciate some help.

Thank you and have a nice day

Tilen

**StephenR** · 02-22-2012, 06:39 AM

What is the value of ubound(v) when it crashes?

**Lynx2x** · 02-22-2012, 07:22 AM

hi!

Yes, I see it now. I looked up where is the problem, and problem is that some of the cells in column A are empty. So when it comes to an empty cell it chrashes. What should I add to a code, so that if it comes to an empty cell it countinoues on the next full?

tnx

**StephenR** · 02-22-2012, 07:54 AM

Wrap the two middle lines in the If statement:

Please Login or Register  to view this content.

**Lynx2x** · 02-22-2012, 08:09 AM

Yeah I though that this I could fix with some If sentence, but in VBA I'm really poor, so thank you very much, because it work fine now.

Finding certain word in cell....then split text

LinkBack

Thread Tools

Rate This Thread

Display

Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Re: Finding certain word in cell....then split text

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions