Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text is being converted to Date
I have my cell as "text"... here is an example of what is in the cell...
08-10-20-00-00-00 I need to do a Replace....Replace -00 with (leave it blank) Excel then converts my number to 8/10/2020 Where as I want it to display as 08-10-20 Please help and thanks in advance, Kacy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text is being converted to Date
Put a single apostrophe before your text before the find/replace.
-- Gary's Student "kbreiss" wrote: I have my cell as "text"... here is an example of what is in the cell... 08-10-20-00-00-00 I need to do a Replace....Replace -00 with (leave it blank) Excel then converts my number to 8/10/2020 Where as I want it to display as 08-10-20 Please help and thanks in advance, Kacy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text is being converted to Date
How about using a helper cell (or column of cells):
=substitute(a1,"-00","") and drag down kbreiss wrote: I have my cell as "text"... here is an example of what is in the cell... 08-10-20-00-00-00 I need to do a Replace....Replace -00 with (leave it blank) Excel then converts my number to 8/10/2020 Where as I want it to display as 08-10-20 Please help and thanks in advance, Kacy -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text is being converted to Date
kbreiss wrote...
I have my cell as "text"... here is an example of what is in the cell... 08-10-20-00-00-00 I need to do a Replace....Replace -00 with (leave it blank) Excel then converts my number to 8/10/2020 Where as I want it to display as 08-10-20 Don't use Edit Replace. Use Data Text to Columns and follow these steps. 1. Choose Fixed Width, and click Next. 2. In the data preview box, place your mouse pointer just to the right of the 0 in -20 and click once. Excel should draw a vertical line just after that 0. Click Next. 3. The data preview box in the next screen of the wizard should show 2 fields. For the first field, which is what you want to keep, set the column data format to Text in the upper right box. Then select the second field (click on it in the data preview box) and in the upper right select Do not import column (skip). Then click Finish. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text is being converted to Date
That worked...Thanks! Out of curiousity what did that do?
"Gary''s Student" wrote: Put a single apostrophe before your text before the find/replace. -- Gary's Student "kbreiss" wrote: I have my cell as "text"... here is an example of what is in the cell... 08-10-20-00-00-00 I need to do a Replace....Replace -00 with (leave it blank) Excel then converts my number to 8/10/2020 Where as I want it to display as 08-10-20 Please help and thanks in advance, Kacy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting number or text to a Date Format | New Users to Excel | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Combine date with text | Excel Worksheet Functions | |||
EXTRACT TEXT FROM A DATE | Excel Worksheet Functions | |||
Date format within a text syntex? | Excel Worksheet Functions |