Data Repeating Characters into Muliple Columns

**stva1006** · 11-30-2012, 05:24 PM

I have tens of thousands of rows of data that come in like the following:
This+is+a+test
The problem is I have to break this down and put it all together in multiple ways. Everything before the first Plus needs to remain intact and keep the +, but all remaining pluses need to be changed to "-". Then I need to have everything in between the "-" broken into columns of which those individual elements need to be edited using another list using vlookup, edited and then concentated back into one column with the delimiters.

Its a little more complicated because some have multiple +'s, some have just one and some have +'s and -'s. I have attached a sample. I've made some progress but need everything from column E and on sorted so I can do the vlookup on them.

Oh and everything using vlookup needs to be changed to something else. Some are already correct and output to the same thing.

Sample.xlsx

**djapigo** · 11-30-2012, 05:36 PM

Hi stva1006,

Have you tried Text to Columns?

Higlight your cells that you want separated, pres Text to Columns, Delimited, Next, Delimiters, check only Others: + (plus sign), Next, Finish

Then go back and add the + sign back to the first column (use a helper column and use the formula = A1&"+")

Let me know if this didn't work.

Dennis

**stva1006** · 11-30-2012, 05:40 PM

Yeah, the problem with that is after those columns then need to be referenced by another sheet or cell to be edited to a different description. And I am dealing with 11,000 rows with 5,500 different combinations of data in ever way possible. I have to do this on a regular basis and its extremely time consuming. I am trying to create a template that these can be imported into with the least amount of tweaking possible then exported to a .csv

Shawn

**djapigo** · 11-30-2012, 06:10 PM

Hi Shawn,

You can record it into a macro and simply create a button to do all the work.

I suggest you look up in the VBA/macro section for parsing data. I'm sure there are plenty of code that people have come up with.

Good luck!