Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup format problem
How do I change data format so that vlookup will recognize that it is a match
for the desired value? I've tried copy, paste special, custom formatting, etc. but when I select a particular cell, the value that appears on the formula bar is still in the wrong format, even though the formats appear correctly in the column. The original data comes over as general "3.40111E+12", in the formula bar it appreas as "34011120010001" and I want it to be "34-0111-2001-001". |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup format problem
I suggest first transforming your data so it actually is hyphenated. Enter
this small macro: Sub xformt() Dim s As String For Each r In Selection v = r.Value s = Left(v, 2) & "-" & Mid(v, 3, 4) & "-" & Mid(v, 7, 4) & "-" & Right(v, 4) r.Value = s Next End Sub Select the cells you want to change and run the macro. -- Gary''s Student - gsnu200731 "Patio" wrote: How do I change data format so that vlookup will recognize that it is a match for the desired value? I've tried copy, paste special, custom formatting, etc. but when I select a particular cell, the value that appears on the formula bar is still in the wrong format, even though the formats appear correctly in the column. The original data comes over as general "3.40111E+12", in the formula bar it appreas as "34011120010001" and I want it to be "34-0111-2001-001". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP problem | Excel Discussion (Misc queries) | |||
VLookUp problem | Excel Worksheet Functions | |||
vlookup Problem | Excel Discussion (Misc queries) | |||
VLookup Problem | Excel Discussion (Misc queries) | |||
Vlookup Problem | Excel Worksheet Functions |