Home

Cell Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-390028%22%20slang%3D%22en-US%22%3ECell%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20need%20help%20with%20cell%20formatting.%20I%20am%20pulling%20from%20a%20file%20that%20has%20numbers%20displayed%20as%20text.%20I%20am%20trying%20to%20format%20that%20number%20and%20then%20copy%20and%20paste%20to%20a%20Vlookup%20file%20to%20auto%20populate.%20My%20issue%20is%20that%20when%20I%20paste%20that%20number%20it%20displays%20the%20right%20format%20but%20in%20the%20formula%20bar%20it%20is%20the%20original%20format%20causing%20the%20V%20lookup%20not%20to%20work.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EExample%3A%2048221234%20%3B%20I%20custom%20format%20it%20to%2048-22-1234.%20In%20the%20cell%20it%20reads%2048-22-1234%20but%20in%20the%20formula%20bar%20it%20still%20says%2048221234%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100398i8C3ACA688AFED830%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20alt%3D%22Excel%20help.JPG%22%20title%3D%22Excel%20help.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-390028%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390273%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390273%22%20slang%3D%22en-US%22%3EIn%20your%20VLOOKUP%20formula%2C%20use%20the%20number%2C%20without%20any%20custom%20formatting%2C%20as%20the%20lookup_value%20argument.%20Custom%20formats%20are%20for%20visual%20representations%20but%20are%20stored%20as%20the%20same%20number%20by%20Excel.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390122%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390122%22%20slang%3D%22en-US%22%3E%3CP%3E...%20or%3C%2FP%3E%3CP%3E%3D%20TEXT(%20V%2C%20%2200-00-0000%22%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390065%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F309760%22%20target%3D%22_blank%22%3E%40AveryN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENumber%20formatting%20does%20not%20change%20the%20number%20as%20read.%26nbsp%3B%20For%20that%20you%20would%20need%20something%20more%20like%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D%20LEFT(V%2C%202)%20%26amp%3B%20%22-%22%20%26amp%3B%20MID(V%2C%203%2C%202)%20%26amp%3B%20%22-%22%20%26amp%3B%20RIGHT(V%2C%204)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ewhere%20V%20is%20the%20value%20you%20wish%20to%20format.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
AveryN
Occasional Visitor

I need help with cell formatting. I am pulling from a file that has numbers displayed as text. I am trying to format that number and then copy and paste to a Vlookup file to auto populate. My issue is that when I paste that number it displays the right format but in the formula bar it is the original format causing the V lookup not to work.

 

Example: 48221234 ; I custom format it to 48-22-1234. In the cell it reads 48-22-1234 but in the formula bar it still says 48221234

Excel help.JPG

3 Replies

@AveryN 

Number formatting does not change the number as read.  For that you would need something more like

= LEFT(V, 2) & "-" & MID(V, 3, 2) & "-" & RIGHT(V, 4)

where V is the value you wish to format.

... or

= TEXT( V, "00-00-0000" )

In your VLOOKUP formula, use the number, without any custom formatting, as the lookup_value argument. Custom formats are for visual representations but are stored as the same number by Excel.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies