I have a similar problem. I collect data from many sources in the form of an MS Word table. I then copy the tables received from each source onto the clipboard and start an Excel macro that pastes the entire table into a spreadsheet. Other sheets in the Workbook then analyze the data and build scripts for posting the data to a database. Feedback is then sent to the source for confirming the data received. But if they have typed "February 1979" as an answer into a cell in the Word table, what gets stored in Excel is "01-02-1979". And if they have typed "2/79" into the cell, Excel stores it also as "01-02-1979". So there is no way that I know of by which I can automatically send back to the source exactly what they have typed. I can either choose a particular output format and everyone gets it back in that format, or for each form received I have to painstakingly retype the data into the Excel sheet with a single quote mark in front.

Suggestions welcome.


Question related to https://www.msofficeforums.com/excel...doing-any.html

Hi
please do not hijack other threads. If you have a question, start a thread of your own, and, if necessary, add a link to the thread you found.
I did it for you this time
Cheers

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

One possibility is to format the entire destination sheet as "Text".

I think this will preserve all pasted data as is.

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

That works with typing but not with pasting. You can easily try it yourself. Open a blank worksheet, format one column as General and one column as Text. Type "Feb 79" in a cell in both columns. They are displayed differently. Then type "Feb 79" somewhere else and put it on the clipboard. Paste into both columns. Both columns will show identical date data.

Any other ideas?

If you're going to be analysing data and the dates have any role in that, conversion to a proper date format is more or less unavoidable. Regardless of what the date is in Excel, though, it can be formatted to appear in just about any format you want for pasting into another app. Besides which, why are you 'sending back' anything other than the results of the analysis? It seems pointless to be trying to re-export the data as well.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks for your suggestion! In this case we are just using the date for historical records purposes, and sometimes all someone remembers is the year and not the month. When they know the month we want to record and display that, and when they only know the year then we want to record and display only that. Thus I just want to capture whatever characters they have typed and store them. I guess it is not possible without retyping into Excel.

If someone inputs merely a year number, Excel won't convert that to a date, so there's no issue there; it's only where a month is input that Excel might do a conversion - and then always to the 1st of the month if no day is input. Easy enough to handle without re-typing anything.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes, good point, but then how to structure a single formula that will deal with both possibilities and give a meaningful result? ISTEXT will be false for both and ISNUMBER will be true for both. YEAR gives the correct result when applied to the Month and Year combination, but when applied to the single four digit year it treats it as a Julian date and gives "1905". The only thing I could come up with, since none of the dates will be from the early 20th century is the following:

=IF(YEAR(E3)>1950,TEXT(E3,"mmmm") & TEXT(E3,"yyyy"),E3)

That should do it!

Thanks for your help!

If you're only interested in the year part, you could use:
=IF(LEN(A3)<5,A3,YEAR(A3))

In a situation where some data have only years and other data have months & years, that would seem to be the most consistent way of analysing them; otherwise you'd need to analyse the data with months & years separately from the data with years only, to avoid skewing the analysis.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]