Parse out contents of cell to new cells

Hello all,

I am a new forum member but love working in excel.

My management has given me a very tedious task of taking the contents of one column in a 16,000 line-item file where the data in each cell of this column has a mix of words and numbers and breaking it up in the following columns: Part number | Price 1 | Price 2
I have to get rid of all the other content in the cell.
So in the example below I would have to take this cell:

UNAUTHORIZED RETRO DC2Z-10346-B 13129.1756 VS 12961.584961

And break it up to:
DC2Z-10346-B | 13129.1756 | 12961.584961

Please note that in another example the cell can have multiple p/n and prices. For instance:
DA8Z-19703-A 112.6535 vs 119.2517 DG9Z-19703-A 112.2381 vs 1149.0433 DB5Z-19703-A 116.1820 VS 122.7945

There are always a part number and price 1, price 2 structure in those fields.
So the second example above would be broken into:

DA8Z-19703-A | 112.6535 | 119.2517 | DG9Z-19703-A | 112.2381 | 1149.0433 | DB5Z-19703-A | 116.1820 | 122.7945

The end of the p/n does not always have a dash, it could be numeric or alphanumeric if that helps.

Thank you kindly for any help on this...

Without a sample text file, it is difficult to come up with specifics, but I'd try the Text to Columns command on the Data menu.

1) Select the column containing the text
2) Text to columns command
3) Delimited
4) Space as delimiter
5) If desired, skip the first two columns containing UNAUTHORIZED RETRO and any columns containing VS
6) End of Text Wizard.

Hi there,

I am familiar with the text to column function and it works for a few lines but with 16k line items it is too much to line them all up because the p/ns and prices are not all lined up. Every extra space or word in that comments field will put them in a different order than the line below.

I tried to attach a sample file but due to our network security settings they have blocked that capability.

The next best option is to paste some data here:

Invoice Comment
483975 CM5E-9H487-AH 124.3009 vs 123.665
499616 CM5E-9H487-AH 124.3009 vs 123.665
501868 CM5E-9H487-AH 124.3009 vs 123.665
506268 CT4Z-19B555-A 161.38 vs 137.3026 CT4Z-19B555-B 167.15 vs 137.3026 CT4Z-19E616-A 8.55 vs 9.18
512903 CT4Z19B555A 161.38 vs 137.302600 CT4Z19B555B 167.15 vs 137.302600 CT4Z19D888A 3.75 vs 18.730000 CT4Z19E616A 8.55 vs 9.180000
236645 CT4Z-19D888-A 3.75 vs 18.73
484715 CT4Z-19E616-A 8.55 vs 9.18
486982 CT4Z-19E616-A 8.55 vs 9.18
97529998 DA8Z-19703-A 119.882 vs 119.729080 DB5Z-19703-A 123.3976 vs 123.235360
97529997 DB5Z-19703-A 122.05 vs 123.5947 temp price in as400 ar adj 13279 to move memo to invoice
490544 DG9H-19D623-AC 50135.1674 vs 50140.1674
431393 DS7T-10300-DB 88.54 vs 88.1535
446194 UNAUTHORIZED RETRO 9C3T-10300-AC 65.8157 VS 64.9783
527661 UNAUTHORIZED RETRO 9C3T-10300-AC 67.4399 VS 65.741730
97573588 UNAUTHORIZED RETRO 9C3T-10300-AC JAN 65.7232 VS 64.8359 FEB 65.8157 VS 64.9285
97530004 UNAUTHORIZED RETRO 9W7T-10300-AA 65.8661 VS 65.070709
486980 UNAUTHORIZED RETRO 9W7T-10300-AA 65.8661 VS 65.070709

Thanks

try

Please Login or Register  to view this content.

Hi Leith,
Thank you for the response, unfortunately there is no particular order or pattern or p/n length with the part numbers as they are p/n provided by our customers and Ford or Chrysler or GM or Hyundai use their own p/n structure.
So it could be any combination of characters or numbers that can contain dashes or not. Some start with numbers and end with letters, some start with letters and end with numbers and some are pure numbers.
The only sure thing that I think we can go by is that they will always be separated by spaces and that we can exclude ALL words from the output like Unauthorized, retro, vs, and so on.

Jindon,

Thank you very much for the code, it works beautifully. How can I adjust the range though? I used it on a sample and it only broke down from line 8 to line 25. Also it skipped the comments that started with "UNAUTHORIZED RETRO 12597567 4.05 VS 2.86"
We are definitely getting close and this is going to save me a TON of time as I will have to update the report every 2 weeks.

Thank you in advance,

Vassili

Try change pattern to

Please Login or Register  to view this content.

Thanks Jindon,
I did and the results came back the same, blank where the Unauthorized retro comment is and the breakdown is still from line 8 to line 25. Here is the data I am using:
92050242 25842351 42.7000 VS. 30.4240 UAUTHORIZED RETRO
92051150 25842351 42.7000 VS. 30.4240 UNAUTHORIZED RETRO
92047427 25842351 42.7000 VS. 30.4250
92048307 25842351 42.7000 VS. 30.4250
92048998 25842351 42.7000 VS. 30.4250 UNAUTHORIZED RETRO
92044644 25847919 11.1700 vs.10.9920
747764 25847919 6.08 vs 7.40
795432 25847919 6.0800 vs 7.40
244448 CM5E-9H487-BA 130.4075 vs 129.3264
246080 CM5E-9H487-BA 130.4075 vs 129.3264
247742 CM5E-9H487-BA 130.4075 vs 129.3264
247743 CM5E-9H487-BA 130.4075 vs 129.3264
249305 CM5E-9H487-BA 130.4075 vs 129.3264
97124524 UNAUTHORIZED RETRO 12597567 4.05 VS 2.86
201617 CR3T-10300-AC 86.57 vs 89.7102
204749 CR3T-10300-AC 86.57 vs 89.7102
205804 CR3T-10300-AC 86.57 vs 89.7102
711584 25882578 3.14 vs 2.2804
678196 25882578 3.14 vs 2.2804 88969918 18.37 vs 18.73
722517 25882578 3.1400 vs 2.2804
159869 25897650 16.8507 vs 16.4050
316702 25925447 5687.88 vs 7370.6169 Part 25925447 Paid on CK# 000075830 usd billed 71.0985
508885 25925447 7132.2778 vs 7156.9448 05GMCTN-12-0270M 4/5/12 WLL
322030 25925447 7351.5465 vs 7370.6169
233192 28037131 21.728 VS 21.0756
92130461 UNAUTHORIZED RETRO 12597567 4.05 VS 2.86
92490895 UNAUTHORIZED RETRO 12597567 4.05 VS 2.86


And here is the code i am testing with:


Option Explicit

Please Login or Register  to view this content.

Thank you

Sorry, here it is as copied from VB:

Please Login or Register  to view this content.

It is working here.

If your data doesn't look like the attached, I need to see the data in workbook.

It works great, thank you very much!
I clicked on the star under your name

Thanks again,

Vassili

You are welcome and thanks for the feedback.