Forum Discussion
Cell Formatting
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
3 Replies
- 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.
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" )
