Organizing Excel columns based on certain text

Occasional Contributor

There is a big amount of data that needs to be re-format for other tools.

Customer send the data in following format, where the 123X00 means, that parts from manufacturers a, b or c can be used. The rule applies to only codes ending with X00.

See below.


123X00dsfsfa sdfsdfsbasdfasdfc


Our system can input such data only in format, where 123X00 is written on separate rows, such as below. So the customer data must be "transformed" into format below.






Do you have an idea, how to get such re-formatting automated, as there are thousands of rows that can not be handled manually

10 Replies

I prepared a VBA. Let's see if it is helpful to you.


Thanks. I can see you know VBA well.  I can see my original description was not clear.

Attached some of the real data with example what the real result should look like. I can understand little bit what is happening with the VBA code you created, but I am not able to fix it from there..

I discovered that I need more complex VBA. I need to list all the part numbers and manufacturers for each Customer code ending with X00. On top of that, I need to list each separate code as a new row. Please see attached example... I try to ask local help also, but would like to see if this is possible to do with VBA.


Please attach the sample for reference.

Here a shorter version (with reduced data)


Eh, once again


Sorry.... I do not see any attachment in your article.


Now attached to this message. Do you see it?


I prepared another VBA for your reference. It also included more comment. Hope that it is helpful. 




Thanks, I could not find the VBA inside the file. Can you please re-check? OR maybe I don't know how to use it....?


br, Jani


Sorry.... I don't know why the file does not contain VBA..... I re-write it again. Please see the attachment.

Related Conversations
IF statements and conditional formatting
clare1981 in Excel on
0 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
0 Replies
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
3 Replies