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
Highlighted
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
Highlighted

@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.

Highlighted

... or

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

Highlighted
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
Documentation Migration
SunLeo in Office 365 on
0 Replies
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies
conditional formatting with formula
kdwork in Excel on
3 Replies
Relative conditional formatting
melissach in Excel on
8 Replies