Home

Hiding numbers within an account number

%3CLINGO-SUB%20id%3D%22lingo-sub-897945%22%20slang%3D%22en-US%22%3EHiding%20numbers%20within%20an%20account%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897945%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20is%20the%20formula%20for%20hiding%20the%20middle%20three%20digits%20in%20an%20account%20number%20with%20a%20dash%3F%26nbsp%3B%20I%20have%20accounts%20that%20look%20like%20%22ABC-123456%22.%26nbsp%3B%20I%20am%20trying%20to%20put%20XXX%20in%20for%20the%203%20digits%20in%20the%20middle.%26nbsp%3B%20I%20want%20it%20to%20look%20like%20%22ABC-XXX456%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-897945%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-897962%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20numbers%20within%20an%20account%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897962%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421604%22%20target%3D%22_blank%22%3E%40BMR01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eusing%20your%20example%20(abc-123456)%20you%20could%20use%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3D%3CSTRONG%3Ereplace%3C%2FSTRONG%3E(%3CSTRONG%3Eold%20text%3C%2FSTRONG%3E%20%7Bthe%20cell%20with%20the%20original%20act%20%23%7D%2C%20%3CSTRONG%3Estart_num%3C%2FSTRONG%3E%20%7Bhow%20many%20units%20into%20the%20text%20you%20want%20to%20start%20replacing%7D%2C%3CSTRONG%3Enum_chars%3C%2FSTRONG%3E%20%7Bhow%20many%20characters%20will%20be%20removed%7D%2C%20%3CSTRONG%3Enew%20text%3C%2FSTRONG%3E%20%7Bwhat%20you%20want%20displayed%7D)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eall%20in%20all%20this%20is%20the%20formula%20to%20go%20from%20abc-123456%20to%20abc-xxx456%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3Dreplace(A1%2C5%2C3%2C%22xxx%22)%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-897983%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20numbers%20within%20an%20account%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897983%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F149914%22%20target%3D%22_blank%22%3E%40katrina%20bethea%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20that%20worked.%20I%20was%20trying%20to%20link%20the%20function%20LEFT%20and%20RIGHT.%20It%20was%20just%20not%20working.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%26nbsp%3B%3C%2FP%3E%3CP%3EBrian.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
BMR01
New Contributor

What is the formula for hiding the middle three digits in an account number with a dash?  I have accounts that look like "ABC-123456".  I am trying to put XXX in for the 3 digits in the middle.  I want it to look like "ABC-XXX456"

2 Replies

@BMR01 

 

using your example (abc-123456) you could use;

=replace(old text {the cell with the original act #}, start_num {how many units into the text you want to start replacing},num_chars {how many characters will be removed}, new text {what you want displayed})

 

all in all this is the formula to go from abc-123456 to abc-xxx456;

=replace(A1,5,3,"xxx")

@katrina bethea 

Thank you, that worked. I was trying to link the function LEFT and RIGHT. It was just not working. 

 

Thank you, 

Brian. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies