Text is being converted to Date

K

kbreiss

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
 
D

Dave Peterson

How about using a helper cell (or column of cells):

=substitute(a1,"-00","")
and drag down
 
H

Harlan Grove

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
Click to expand...

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.
 
Top