Excel 2007 : Advanced text to column data manipulation

[ashd]

Hi there,

I have tried looking for an answer on the forums but couldn’t find anything. I don’t have much excel knowledge, just the basics.

I have a single cell of product specification data that I would like to separate into multiple columns, basically listing the product specifications in their own columns

The problems when doing a text to column filter I face are:

1. Although the different specifications are generally separated by commas, there are errors where the comma hasn’t been inserted at time of capture resulting in 2 specifications occupying the same cell.
2. Sometimes a comma has been used within a certain specification instead of a period resulting in one specification separated into multiple cells.
3. Not all product specifications are the same. i.e some products will have one specification but not another and/or there are different values associated with each specification.

How does one consolidate the data solving 1 and 2
How does one sort the relevant specification so they get inserted in the correct column? There are keywords which can be used as an identifier.

I have attached a worksheet showing examples and issues

Any help would be greatly appreciated
Thanks
~W

[dilipandey]

Hi ashd,

Welcome to the forum.


As there is large text in the cell.. I would first do a Ctrl + H (Find & Replace) and put a kind of special character may be ~ or ^ etc.. which is not existing there and then I will do a text to column basis these special characters

Regards,
DILIPandey

<click on below 'star' if this helps>

[ashd]

Thanks DILIPandey,

I didnt think of that and its a technique I might use, thanks.

Luckily I found a constant where the comma was always put into a single specification. So I have ,yadda (yadda ,*MB),. So the “*MB)” ends up in its own cell.

The first formula finds the "mb)" which has been put in the next cell but im struggling with how to concatenate it with the previous cell using the indirect reference
Is it possible that you could guide me with how to put this all into a single formula? Ive never used many formulas in Excel so its all a bit of a steep learning curve

=ISERROR(SEARCH("MB)",$A$1:$Z$1))
=IF($A$1:$Z$1,"","concatenate the 2 cells here")
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

My thinking is that there will only be a few variations of this happening and I could cover them all by adjusting this formula for each occurence.

~W