How convert the cell data to its formatting style?

Erkronos

New member
Joined
Oct 26, 2015
Messages
3
Reaction score
0
Points
0
Hello,

I need to know how to convert formatting style to its native data? For example if a cell has '0000101' value and with custom formatting I convert into '00-00-10-1' but I want that formatted style to be written in its data. Formatting are just visual. The data itself is not altered at all. Is it possible to do such action? Thank you.
 
Formatting are just visual.
If I understood you, try
Select Cell => Format Cells => Custom => 00-00-00-0

The data in cell is value 101 but is displayed as a 00-00-10-1

I'm sorry if I'm not well understood
If your value is entered as '0000101 (with apostrof), then in the next column B put the formula =VALUE(A1) and custom format this cell
You can hide column A
 
Last edited:
I did that formatting by using Custom format. My value does not have any apostrophe. It is just 0000101. With custom formatting it becomes 00-00-10-1 visually. Still the native data is 0000101. With this new formatting I want to change the native value just like its visual style.
 
My value does not have any apostrophe. It is just 0000101.
The value 0000101 in Excel cell can not be. Unless written with an apostrophe or a cell formatted as text.
Sorry, I do not understand. It might help someone else
Regards
 
With this new formatting I want to change the native value just like its visual style.
I've been thinking about your problem
Sorry, English is not my native language
Maybe you want to separate digits value

If value 0000101 in cell A1
In cell B1, put the formula
Code:
=CONCATENATE(LEFT(A1;2);"-";MID(A1;3;2);"-";MID(A1;5;2);"-";RIGHT(A1;1))
Result in B1 is 00-00-10-1
 
Thanks but that is a long and inefficient method. I need something better like if I change 0.42 to 42% with percentage formatting I want the written to become 42% not 0.42. This goes for all other stuff.
 
Back
Top