Recover Date from Text to Column and Concatenate

[paulmacro]

Here is my Workbook Recoverdate4.xlsx with 3 desires

1. The formula in A3 to work (show date only and with or without time) for the raw data in B2 (there is a space at beginning that can't be avoided at present)
2. The raw data in B2 is Text to Column in B3 (fixed width) & B4 (delimit). The formula in A3 should account for this & show the date for either variation if possible.
3. Recover the date from the data being concatenate, examples A8 & A9

My Best Regards

Paul

[mcmahobt]

Could you not just use this in A2 and drag down?

Please Login or Register  to view this content.

[paulmacro]

Thanks Mcmhabobt, yes =LEFT(B2,17) can work if space removed at 1, how do I ignore a space at 1 or how do I get =LEFT(B2, 2-18) to run?

[mcmahobt]

Would

Please Login or Register  to view this content.

work?

[newdoverman]

In A3 use this version of the formula and the leading space will not be there

Please Login or Register  to view this content.

[paulmacro]

Recoverdate2.xlsx
Thanks very much mcmahobt yes TRIM(LEFT (B2,17) ) does work see new workbook Row 2 & 10, can I merge it with the existing formula in A3? =(MID(B3,10,2)&" "&MID(B3,6,3)&" "&LEFT(B3,4)&" "&RIGHT(B3,5))+0 so to ignore if there is a space or not.
Any trick, idea, for a concatenate at B8 &9?
Cheers
Paul

[mcmahobt]

Before you import your data, could you do a CTRL+F Find/Replace for " 2015" and replace with "2015" to initially remove that space, then go Text to Columns delimited with a space to separate your data? That, at least to me, seems like it would help keep your formula much simpler.

Newdoverman may be able to offer a sufficient response using formulas, but this seems like a better option to me.

[paulmacro]

Thanks mcmahobt, yes I use that kind of thing, find and replace for a fix, but need a better way to be more specific (meet all conditions or criteria), eg if data contains multiple years, with the find & for it to hold within a macro.
Excel 2010 can change the format of the Text to Column by delimit such that the Time column can be concatenated as text & therefore the A3 calculation can work there to but column must be specific I think.
Cheers
Paul

[paulmacro]

Newdoverman, Thanks very much, neat & tidy this "Trim", its new to me, I'm beginning to like it!

May I ask for some more help: Is it possible to modify the formula further? to accommodate additional text (Cell A2) or if only a date is present (Cell A5) or if the cell is blank move to next (Cell A4)here is the workbook updated with your formula Recoverdate4.xlsx & our desires

Thanks again all, this really does help

Paul

[mcmahobt]

I'm not sure if I'm still quite understanding the problem. If all you are trying to pull is the date from column B (if there is one), and if not pull the data in column C, then try this in A2 and copy down:

Please Login or Register  to view this content.

Otherwise, hopefully Newdoverman can come to the rescue.

[paulmacro]

Thanks mcmahobt, your TRIM method coupled with the Text to Column "text format" will solve the main problem of the concatenate recovery using Norie A3 formula. I was hoping that Newdoverman might be able to give a formula that fits all the circumstances, makes it all the more easier before I go ahead with the macro writing.
Have a great weekend
Paul

[newdoverman]

Can you upload another workbook that had untouched data that has been imported that has all the variations that will be encountered. mcmahobt has raised some very valid points in his posts. In the beginning message you wanted the date. Then, it included the time. I am also becoming confused as to what the beginning situation really is and what the end result should be.

A new workbook with a very clear set of parameters for the solution would be appreciated. I am sure that among us, we can come up with a viable, workable solution for you.

[paulmacro]

Dear all, this is my first thread with a workbook of my own so sorry if I'm not up to speed yet, on the learning curve, with how to do in this forum.
I started this thread following advice from Norie who wanted a fresh workbook because Savi and perhaps others were becoming confused. Norie is the origin of the formula in A3, Savi does it differently as does punnam.
Basically my understanding was that the formula in A3 converts text or a "scrabble" of text, spaces & numbers into a number format that excel converts easily to date (including the time) number. The "excel date number" can then be easily reformatted into a date or time or combination as standard or custom date-time formats.
My initial problem with Norie formula was if target cell started with a space then error #value! occurred
We made some progress with TRIM and the "text to column" format an individual column (to concatenate a time as text into a date).
Perhaps I made a mistake in updating the original document & editing the 1st post? okay, so what I'll do this time is write a new workbook with a clear set of parameters that is based on what I know now and put it in as a edit post #1. I'll try not to be confusing or ask to many parameters or desires. Or do I start a whole new thread? I'd rather just keep this one thread & the new workbook. This is only a small piece of a much bigger puzzle, project so I wanted this piece (A3 formula) to cover as much as possible.
With hindsight, I should have written a more detailed "Background" on the original post?
Thanks again for all your help
Best Regards
Paul

[popipipo]

Maybe something like this?

[paulmacro]

Hi popipipo Thanks very much, I can work with that here it is on workbook. It would be nice if the formula held if date only in a cell eg rows 8,10,13,15. The space at the beginning, before anything is a pain though, be good to have an easy way to remove if present.

Have a great weekend Wilem
Cheers
Paul

PS Marked as solved now, decided to simply add a space to the beginning of all data via concatenate then separate with text to column Delimit, treat all consecutive space as one and with column format as text. "TRIM" and "LEFT" excellent for next step which moving onto now. Thanks again all for your valuable contributions.

[paulmacro]

Here is the solution solved for the time error with =DATE(LEFT(TRIM(B3),4),MONTH(MID(TRIM(B3),6,3)&1),MID(TRIM(B3),10,2))+MID(TRIM(B3),14,5) EG when 10:00 etc