CODEWITHSUNDEEP
excelvbaexcel-formulacell-formatting
davood asgharzadeh
davood asgharzadeh

Reputation: 43

change cell format to general or text?

we have sort of data in excel like this ---> 34:5:20 excel likes to consider it as time or date. here is the problem because I know it's not a date and what ever action I want to do with it again excel thinks it's a date/time value . how can I stop this behavior ? (each of the ':' delimited values has a special meaning to me but not date/time) thanks a lot.

Upvotes: 2

Views: 2279

Answers (2)

davood asgharzadeh
davood asgharzadeh

Reputation: 43

I found the solution finally .

  1. copy column to notepad.
  2. select another column
  3. change it's format cell to text.
  4. now paste from notepad. now my data is considered as text and I can separate the values using "column to text" from DATA tab. thank you all for your valuable time.

Upvotes: 1

Ranga
Ranga

Reputation: 430

There is one option though, you can use the formula below, to convert the dates back to your format

=CONCAT(" ",TEXT(A2,"[h]:mm:ss"))

I am adding a space using concat function just so that if you ever decide to convert all to value, it will not return back to dates

Upvotes: 1

Related Questions