Rearrange non standard data to a format "text to columns" will recognize

[donojohn]

Hi again gang,

Last dilemma I'm having with a data migration spreadsheet:
We have data in a cell that should include 3 separate expiration dates, some cells have all 3 dates in the right order, some have all 3 dates out of order and some are missing the dates... I was working unsuccessfully to try and format them into similar formatting, then sort the data by row using "text to column" such that I get all 3 columns populated with the appropriate date and cell missing a date with some N/A or MISSING note.

The attached spreadsheet has the first few rows and columns are what I need the output to look like.

I appreciate all the help.

[AliGW]

Which version of Excel are you using? Please update your user profile.

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[Pete_UK]

Are you saying that the first 3 rows of your file are what you want to end up with (i.e. dates in the correct column), and that rows 5 to 25 are what you might end up with after doing text-to-columns, and that rows from 26 onwards is what your data looks like to start with (i.e. up to 3 dates in a single cell, with 2 line-feeds between and maybe some #N/A errors?

Pete

[donojohn]

Exactly Pete,

The top 3 rows are the desired outcome.
ROw 5-25 are what I get if I manually adjust the data in cell A# to get it into comma delimited format and just "text to column" - my thought was maybe I could then sort by row?
Column A of Rows 26 on are the data as the software exports them ... I tried unsuccessfully to use replace and TRIM to eliminate the extra spaces and to replace the "yyyy-mm-dd" dates with "yyyy-mm-dd," ... and so I turn to you guys, who as I continue to learn the power of Excel and equally my own current limitations, I figure in one nested formula you guys can achieve what I've already spent hours unsuccessfully on. This is just a portion of the much larger dataset.

The reality is that so often I give up and just spend the time manually manipulating the data, because its faster than learning how to use the proper function/formula... but as I'm back in the office of the family's small business full time again, I'm determined to learn this time.

[Pete_UK]

Using your existing file, I put the headings Bordetella , DHPP , and Rabies in cells D1, E1 and F1, and then used this formula in cell D26:

=IF($A26="N/A","#N/A",IF(COUNTIF($A26,"*"&D$1&"*"),MID($A26,SEARCH(D$1,$A26),LEN(D$1)+12),""))

This can then be copied across into E26 and F26, and then the 3 formulae can be copied down to row 36.

No need for Text-to-columns, or TRIM etc.

Hope this helps.

Pete

EDIT: file attached …

[donojohn]

Wow Pete - Thank you! I will spend the next few hours following the logic through on the formula, but wow. I am truly appreciative!

[Pete_UK]

Glad to help, and thanks for marking the thread as solved.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete