Select and copy text after specified word macro

**pongi** · 05-09-2012, 06:20 AM

Hy!

I have a huge ammount of exported data, and one cell contains the category name and the category path too. I want to write a macro, which can find the category name, copy and paste it to an other cell, and the same for the path too.

Example:

Cell looks like this:
Category Name: ProductNameSomething, Category Path: Materials/Alloys/Something

And I need the macro to copy the ProductNameSomething and Materials/Alloys/Something to other cells (wothout the "Category Name:" and the "Category Path:").

I would really appriciate some help, how can I select and copy text in a cell after a specified word? Or is there any easier solution?

**jraj1106** · 05-09-2012, 06:22 AM

Hi

Uploading the sample file will be a great help... You can extract few records...

**pongi** · 05-09-2012, 06:28 AM

Right, I made a sample.

The character decoding is superweird, thats a website issue to solve for an other co-worker.

sample.xlsx

**jraj1106** · 05-09-2012, 06:32 AM

There are cells where only category path exists... What do you want to do abt those data?

**pongi** · 05-09-2012, 06:35 AM

skip it (make a blank cell), so I can easily know, there was something wrong.

**tom1977** · 05-09-2012, 06:57 AM

Hi select column A and run this code

Sub aa()
Dim k As Range
Dim x As Integer
Dim ar, temp As Variant
ar = Array("Category Name: ", "Category Path: ")
For Each k In Selection
        x = 0
        temp = Replace(Replace(k.Value, ar(x), ""), ar(x), "")
        x = x + 1
        k.Offset(0, 1) = Replace(temp, ar(x), "")
        Next
End Sub

**jraj1106** · 05-09-2012, 07:55 AM

Try this too:

sample (3).xlsm

**pongi** · 05-09-2012, 07:57 AM

The code is almost perfect! Is it possible, to move only the name into column B and the path into column C?

Edit: the sample, you attached works perfectly! Thank you very much.

**jraj1106** · 05-09-2012, 08:07 AM

Now you can try it again:

sample (3).xlsm

**pongi** · 05-09-2012, 08:09 AM

perfect, thanks!

**jraj1106** · 05-09-2012, 08:11 AM

You are welcome...

If the probs solved then pls click the star below the post...

Select and copy text after specified word macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Re: Select and copy text after specified word macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions