data validation

Copper Contributor

the data that i exported form other program has column with values "8-2271" (the last 4 numbers are different), but in excel show me like date aug.71 in cell, but up in functions show me in format like this 01.08.2271. The right number that i need in my data should to be 8-2271. How to fix it this.

Pictures in attach

8 Replies

@Vlatko1990 

Excuse me for the inconvenience, but we need precise information in order to be able to provide you with an acceptable solution.

I cannot see from your text from which program you are importing this text and not which version of Excel you are dealing with. Knowing the operating system would also be a plus for a suggested solution. An inserted file (without sensitive data) would be best where you could explain your subject.

If it is not absolutely necessary, please do not send a photo, you cannot tell from the photo whether it could be due to the file (if the error only occurs in this file).

To keep it short, a little more info would help.

 

Nevertheless, here ... some information about it.

Format numbers as text

https://support.microsoft.com/en-us/office/format-numbers-as-text-583160db-936b-4e52-bdff-6f1863518b...

Import or export text (.txt or .csv) files

https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce...

 

Thank you for your understanding and patience

 

Hope I was able to help you.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

@NikolinoDE I am using excel 2013. The exporting from the program that i made it, it's in csv format. The program it's made it only for us. I am sending excel document to can see what is the problem. Please see column Box (D). For ex. data in cell D2 should to be 8-1971. From the program where i am exporting data is 8-1971, not like date.

OS? mac, windows? what windows?

@NikolinoDE windows 2007

@Vlatko1990 

If open csv file by Excel it do default transformation which practically is not possible to avoid. Workaround is to import csv file and on third step of the wizard mark proper columns as Text type. Result will be like

image.png

 If I may add this information to Mr. Sergei Baklan (@Sergei Baklan) statement.

As far as my research is based on statement from Mr. Sergei Baklan ,

Microsoft Excel does not check the character set and instead assumes, without checking, that the content of a CSV file corresponds to a Windows-specific character set (Windows-1252).

As a result, when a CSV file is opened, the content is incorrectly decoded and the special characters are displayed incorrectly.

 

Small and quick solution:

To open a CSV file with Microsoft Excel, which uses UTF-8 as character encoding (standard with Cloudrexx), proceed as follows:

You can convert your CSV file e.g. with the free Notepad

  1.            Load the CSV file
  2.            Perform the conversion in the menu under "Encoding / Convert to UTF-8 without BOM"
  3.            3. Save the CSV file.

    I hope we could help you a bit.

     

    Thank you for your understanding and patience

     

    Nikolino

    I know I don't know anything (Socrates)

     

@Vlatko1990

@NikolinoDE 

Excel, if open csv file directly, converts texts like "11-07" into dates as 07 Nov 2020 (depends on locale) independently on which encoding is used.

@NikolinoDE Thank you for assistance, but it's not working. Any way, thanks again...