Link to home
Start Free TrialLog in
Avatar of zhshqzyc

asked on

Format cell

Hi, sos, need format cell.
If I click the first cell, the value is "1" rather than "MC0001".
Also, I want to remove the space in the cell, "MC 1517"==>"MC1517".

Avatar of Matt Coughlin
Matt Coughlin
Flag of United States of America image

Just change the format from custom to General for that column.
Then type "MC0001" in the first cell and fill it down.

To remove a space you would use the following formula:

=SUBSTITUTE(C1," ","")
format your column as custom format "MC"00000

Do a search replace (Ctrl+H) on your column to search for MC and replace with nothing

Well looking at the sheet your using a custom Cell format which add's in MC but doesn't actually add it to the cell.

Currently its "MC "0000 it need's swapping to "MC"0000

But that still won't actually put the MC into the cell as its a format option.

Avatar of zhshqzyc


how to fill in? There is some jump from MC0060 then go to MC 0101.
If you want your values to be converted to text, use the formula
="MC" & text(A2,"0000")
Can somebody give me a clean sheet, I will study your suggestion later on.
Avatar of nutsch
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial