Skip i'r prif gynnwys

Sut i guddio rhan o werth celloedd yn Excel yn unig?

doc cuddio yn rhannol 1
Gan dybio bod gennych ddalen gyda rhywfaint o wybodaeth gyffredinol am y staff, ond mae rhai yn breifat fel rhifau nawdd cymdeithasol. Nawr rydych chi am guddio rhan o'r rhifau nawdd cymdeithasol hyn fel y dangosir isod, sut allwch chi ei ddatrys yn gyflym?

Cuddio rhifau nawdd cymdeithasol yn rhannol gyda Celloedd Fformat

Cuddio testun neu rif yn rhannol gyda fformwlâu

swigen dde glas saeth Cuddio rhifau nawdd cymdeithasol yn rhannol gyda Celloedd Fformat

I guddio rhan o rifau nawdd cymdeithasol yn Excel, gallwch gymhwyso Celloedd Fformat i'w datrys.

1. Dewiswch y rhifau rydych chi am eu cuddio'n rhannol, a chliciwch ar y dde i ddewis Celloedd Fformat o'r ddewislen cyd-destun. Gweler y screenshot:
doc cuddio yn rhannol 2

2. Yna yn y Celloedd Fformat deialog, cliciwch Nifer tab, a dewis Custom o Categori cwarel, ac ewch i fynd i mewn i hyn 000 ,, "- ** - ****" i mewn i'r math blwch yn yr adran dde. Gweler y screenshot:
doc cuddio yn rhannol 3

3. Cliciwch OK, nawr mae rhifau rhannol a ddewisoch wedi'u cuddio.

Nodyn： Bydd yn talgrynnu'r rhif os yw'r rhif sydd allan yn fwy na neu'n eaqul i 5.

swigen dde glas saeth Cuddio testun neu rif yn rhannol gyda fformwlâu

Gyda'r dull uchod, dim ond rhifau rhannol y gallwch chi eu cuddio, os ydych chi am guddio rhifau rhannol neu destunau, gallwch chi wneud fel isod:

Yma rydym yn cuddio 4 rhif cyntaf y rhif pasbort.

Dewiswch un gell wag wrth ymyl y rhif pasbort, F22 er enghraifft, nodwch y fformiwla hon = "****" & DDE (E22,5), ac yna llusgo handlen autofill dros y gell mae angen i chi gymhwyso'r fformiwla hon.
doc cuddio yn rhannol 4

Tip:

Os ydych chi am guddio'r pedwar rhif diwethaf, defnyddiwch y fformiwla hon, = CHWITH (H2,5) & "****"

os ydych chi am guddio rhifau tri canol, defnyddiwch hwn = CHWITH (H2,3) & "***" & DDE (H2,3)

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools for Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed o'r blaen. Kutools for Excel Yn cynnig dros 300 o nodweddion uwch i hybu cynhyrchiant ac arbed amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905

Office Tab Yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
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