Avatar of zhshqzyc
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".

Thanks
test.xls
Microsoft Excel

Avatar of undefined
Last Comment
nutsch

8/22/2022 - Mon
Matt Coughlin

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," ","")
nutsch

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

Thomas
akitsupport

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.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
zhshqzyc

ASKER
how to fill in? There is some jump from MC0060 then go to MC 0101.
nutsch

If you want your values to be converted to text, use the formula
="MC" & text(A2,"0000")
zhshqzyc

ASKER
Can somebody give me a clean sheet, I will study your suggestion later on.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
nutsch

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question