SOLVED

Number Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1833897%22%20slang%3D%22en-US%22%3ENumber%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1833897%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I'm%20constantly%20pulling%20data%20out%20of%20a%20membership%20database%20and%20I%20need%20dates%20for%20some%20of%20my%20transactions.%26nbsp%3B%20The%20problem%20is%20that%20when%20I%20pull%20the%20data%20out%20of%20the%20database%20all%20my%20dates%20come%20out%20in%20text%20format.%26nbsp%3B%20When%20I%20try%20to%20select%20the%20column%20and%20change%20the%20number%20format%20to%20date%20instead%20of%20text%2C%20I%20then%20have%20to%20go%20in%20and%20activate%20each%20cell%2C%20click%20in%20each%20cell%20and%20hit%20enter%20to%20convert%20the%20text%20to%20date.%26nbsp%3B%20It's%20a%20lot%20of%20data%2C%20so%20this%20isn't%20an%20ideal%20solution.%26nbsp%3B%20Is%20there%20a%20shortcut%20around%20this%3F%26nbsp%3B%20What%20am%20I%20missing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20Eileen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1833897%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1834035%22%20slang%3D%22de-DE%22%3ESubject%3A%20Number%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1834035%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F850739%22%20target%3D%22_blank%22%3E%40Eileen123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EWhat%20exactly%20you%20want%20to%20accomplish%2C%20unfortunately%2C%20I%20cannot%20correctly%20take%20from%20the%20translation.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDespite%20the%20test%2C%20I%20will%20send%20you%20the%20information%20that%20might%20help%20you.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3EFix%20text-formatted%20numbers%20by%20applying%20a%20number%20format%3C%2FFONT%3E%3C%2FP%3E%3C%2FDIV%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ffix-text-formatted-numbers-by-applying-a-number-format-6599c03a-954d-4d83-b78a-23af2c8845d0%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ffix-text-formatted-numbers-by-applying-a-number-format-6599c03a-954d-4d83-b78a-23af2c8845d0%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20add%20an%20MS%20Excel%20file%20(without%20sensitive%20data)%20to%20your%20project.%3C%2FP%3E%3CP%3EExplain%20your%20plans%20in%20relation%20to%20this%20file.%20So%20you%20can%20get%20a%20solution%20that%20is%20tailored%20to%20your%20needs%20much%20faster.%3C%2FP%3E%3CP%3EAt%20the%20same%20time%2C%20it%20is%20much%20easier%20for%20someone%20who%20wants%20to%20help%20understand%20the%20subject.%3C%2FP%3E%3CP%3EA%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3EPlease%20no%20Picture%2C%20even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1834114%22%20slang%3D%22en-US%22%3EBetreff%3A%20Number%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1834114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYeah%2C%20I%20know%20how%20to%20change%20the%20number%20formatting%2C%20it's%20just%20that%20I%20always%20have%20problems%20with%20the%20text%20to%20date%20conversion.%20Cells%20do%20not%20convert%20to%20dates%20automatically.%20I%20have%20to%20edit%20each%20cell%20individually%20for%20the%20format%20change%20to%20activate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20change%20these%20text%20%22dates%22%20to%20actual%20dates%20so%20that%20I%20can%20sort%20by%20date%20and%20not%20A%20to%20Z.%20Does%20that%20make%20sense%3F%20I%20have%20version%202009%20-%20OS%20Windows%2010%20Pro%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1834116%22%20slang%3D%22en-US%22%3EBetreff%3A%20Number%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1834116%22%20slang%3D%22en-US%22%3EI%20basically%20have%20to%20edit%20every%20cell%20individually%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi - I'm constantly pulling data out of a membership database and I need dates for some of my transactions.  The problem is that when I pull the data out of the database all my dates come out in text format.  When I try to select the column and change the number format to date instead of text, I then have to go in and activate each cell, click in each cell and hit enter to convert the text to date.  It's a lot of data, so this isn't an ideal solution.  Is there a shortcut around this?  What am I missing?

 

Thanks! Eileen

6 Replies
Highlighted

@Eileen123 

What exactly you want to accomplish, unfortunately, I cannot correctly take from the translation.
Despite the test, I will send you the information that might help you.

 

Fix text-formatted numbers by applying a number format

https://support.microsoft.com/en-us/office/fix-text-formatted-numbers-by-applying-a-number-format-65...

 

With your permission, if I can recommend you, add a MS Excel file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

 

* Knowing the Excel version and operating system would also be an advantage.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

Highlighted

@Nikolino 

Yeah, I know how to change the number formatting, it's just that I always have problems with the text to date conversion. Cells do not convert to dates automatically. I have to edit each cell individually for the format change to activate.

 

I need to change these text "dates" to actual dates so that I can sort by date and not A to Z. Does that make sense? I have version 2009 - OS Windows 10 Pro

Highlighted
I basically have to edit every cell individually
Highlighted
Best Response confirmed by Eileen123 (New Contributor)
Solution

@Eileen123 

 

Maybe the quickest way to do it is to select the whole column

Convert text into dates quickly and easily

You are probably familiar with this: Dates that you take over from other applications often appear as text in Excel. To make real dates out of it, you can edit each individual entry, but it's faster with the following trick:

1. Select the cells whose contents you want to convert.

2. Select the DATA-TEXT IN COLUMNS menu, click on DATA-DATA TOOLS-TEXT IN COLUMNS (Excel 2016/2013/2010).

3. Skip the first two steps of the following assistant with NEXT.

4. Select the DATE option and confirm with FINISH. The wizard then converts the text in all selected cells into date values.
Please try it, should actually work.
 
 
Maybe with VBA code as second option:
 

 

Sub bla()
Dim a as long, i As long
 With Sheets("Excell problem")
  .Columns(1).NumberFormat = "DDD.DD.MMMM.YYYY"
a = .Cells(Cells.Rows.Count, 1).End(xlUp).Row
For i = a To 4 Step -1
        .Cells(i, 1).Value = CDate(.Cells(i, 1).Value)
Next
 End With
Next
End Sub

 

Hope I was able to help you.

Thank you for your understanding and patience

 

And…Please keep asking here - I just taught myself Excel with the help of this forum… nearly

 

Nikolino

I know I don't know anything (Socrates)

 
 
Highlighted

@Nikolino It worked, thanks!

Highlighted
I am happy that I was able to help you.
Wish you a nice day / night with lots of health, joy and love.

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

Nikolino
I know I don't know anything (Socrates)