Problem using VBA code for text to column (the value is repeated after separate cell)

[Jasa P]

I used text to column by selected Tab character as delimiter. So, if I paste data to this workbook, that will be separated in two column.

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

Everything was fine after I got this code that can separate cell (like Text to Column function) and do some other functions (change "X" to "x", etc).

Please Login or Register  to view this content.

But, after pasted the data above, it becomes like this

Please Login or Register  to view this content.

This happens if I paste data in A2, but in A1, it works properly

cats2.jpg
I'm using Excel 2007
I hope there's someone can help me
Any help would be great, thanks a lot

Regards,



Jasa

[Kelshaer]

1. Do you want to split on every TAB character?
2. What are the other functions? for example replacing x with a period and what else?
3. do you copy from the text file and paste in the excel or how do you import the data?

[Jasa P]

I'm sorry for replying too late

1. Yes, I want to split on every TAB character
2. Yes,
- "X" becomes "x" for example: 2438X5X5X5 will be changed to 2438x5x5x5
- after the first "x", becomes "." for example: 2438X5X5X5 will be changed to 2438x5.5.5
3. Yes, I import the data from Word and use paste special: paste text to Excel

Thank you for helping me


Regards