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
not able to type number and text in same cell?
Deleted in Excel on
2 Replies
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies