vlookup format problem

[Guest]

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

 

[Guest]

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.