Skip to main content

How to only hide part of cell value in Excel?

doc hide partially 1
Supposing you have a sheet with some general information about the staff, but some are private such as social security numbers. Now you want to hide part of these social security numbers as below screenshot shown, how can you quickly solve it?

Partially hide social security numbers with Format Cells

Partially hide text or number with formulas

arrow blue right bubble Partially hide social security numbers with Format Cells

To hide part of social security numbers in Excel, you can apply Format Cells to solve it.

1. Select the numbers you want to hide partially, and right click to select Format Cells from context menu. See screenshot:
doc hide partially 2

2. Then in the Format Cells dialog, click Number tab, and select Custom from Category pane, and go to enter this 000,,"-**-****" into the Type box in right section. See screenshot:
doc hide partially 3

3. Click OK, now partial numbers you selected have been hidden.

Note：it will round up the number if the forth number is larger than or eaqul to 5.

arrow blue right bubble Partially hide text or number with formulas

With above method, you only can hide partial numbers, if you want to hide partial numbers or texts, you can do as below:

Here we hide first 4 numbers of the passport number.

Select one blank cell next to the passport number, F22 for instance, enter this formula ="****" & RIGHT(E22,5), and then drag autofill handle over the cell you need to apply this formula.
doc hide partially 4

Tip:

If you want to hide last four numbers, use this formula, = LEFT(H2,5)&"****"

f you want to hide middle three numbers, use this =LEFT(H2,3)&"***"&RIGHT(H2,3)

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (21)
No ratings yet. Be the first to rate!
Vishal
Vishal
This comment was minimized by the moderator on the site
I have 1. Name, so I want to remove "1." Please suggest.
Reply
0
0
Hasan Mehedi
Hasan Mehedi
This comment was minimized by the moderator on the site
Is there any command for format cell, to hide the intended value in cell, that containt with charecters. eg. G1234567Q and after applied the format it would be G****567Q ?Note: I am asking to know the cell format not the formula? Thanks in advance!
Reply
0
0
JL
JL
This comment was minimized by the moderator on the site
If I have a listing of Name with different length, and want to set as "B*** P***" for "Brad Pitt", "M******* Y*** C*** K****" for "Michelle Yeoh Choo Kheng", how can I do it with formula?
Reply
0
0
p
p     JL
This comment was minimized by the moderator on the site
have you found a solution? i would also like to know the answer to this. thank you
Reply
0
0
Vince
Vince     p
This comment was minimized by the moderator on the site
Can replace all but the first char with this: =REPLACE(CELL,2,LEN(CELL),REPT("*",LEN(CELL)))
Reply
0
0
Adric
Adric
This comment was minimized by the moderator on the site
If I have a number and alphabet set like S1234567A and want to put as S****567A, what formula is usable?
Reply
0
0
YY
YY     Adric
This comment was minimized by the moderator on the site
=REPLACE(CELL,2,4,"****")
Reply
0
0
Ashutosh
Ashutosh     YY
This comment was minimized by the moderator on the site
=REPLACE(CELL,1,LEN(CELL)-4,REPT("X",LEN(CELL)-4))is the better version as here you can mask text with variable length
Reply
0
0
Nur
Nur     Ashutosh
This comment was minimized by the moderator on the site
Hi I tried this but does not seem to work.
Reply
0
0
Sunny
Sunny     YY
This comment was minimized by the moderator on the site
Great solution!
Reply
Report
0
0
Kiran
Kiran
This comment was minimized by the moderator on the site
How can we use format cell to remove the first few characters? For ex: 12,345.67 = **,345.67
Reply
0
0
sunil
sunil
This comment was minimized by the moderator on the site
how can i unhide the numbers again
Reply
0
0
Sunny
Sunny     sunil
This comment was minimized by the moderator on the site
Hi, sunil, if you use the Format Cells function to hide part of string, when you want to unhide them again, just format the cells as general.
Reply
Report
0
0
Gaurav Singh
Gaurav Singh
This comment was minimized by the moderator on the site
Nice Sir, Its working
Reply
0
0
P
P
This comment was minimized by the moderator on the site
Sorry, I meant D22
Reply
0
0
P
P
This comment was minimized by the moderator on the site
I am not sure how you wrote the formula Dean, but =LEFT(F22,3)&"-**-****" shows 278-**-**** for me... Maybe check other formats on your destination cell?
Reply
0
0
Sunny
Sunny     P
This comment was minimized by the moderator on the site
Sorry I did not get your question, could you give me a screenshot or other more information to decribe it?
Reply
Report
0
0
Dean
Dean
This comment was minimized by the moderator on the site
Your SSN example fails, in that it rounds up the number, rather than hiding the non-visible placings. In you example, 278-53-6128 is displayed as 279-**-****!
Reply
0
0
Sunny
Sunny     Dean
This comment was minimized by the moderator on the site
Sorry, could you give me more information about the fails in your examples? Because it works always in my examples.
Reply
Report
0
0
BOb
BOb     Sunny
This comment was minimized by the moderator on the site
Dean is correct, the image you are using for the example shows staff no 2083 having the social security number of 278536128 but the hidden version starts with 279. The formula itself works, just the first row in the example image is incorrect.
Reply
0
0
Sunny
Sunny     BOb
This comment was minimized by the moderator on the site
Thanks for the reminder. The format cell method will round up the number if the fourth number is larger than 5.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations