Cell Formatting

Copper Contributor

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.