AboutTom Ogilvy Expertise Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
Question QUESTION: I have a list of contract code numbers with nine digits, the 1st one being a zero. In order to display the zero I put a ' at the beginning of the number.
I need to look these up against a validation list, but of course the zero is suppressed.
So when I try a lookup it fails.
How do I convert the lookup list so it will have a displayed zero so I dont get the n/a message?
I tried concatenating a ' plus the number but this doesnt work. Any ideas Tom
thanks
ralph
ANSWER: ralph,
----------------
it could probably be done with VBA code. Send me a sample workbook (small - I don't need 30,000 rows of data - just enough to see what you want to do). because I am not sure what you exactly want to do.
twogilvy@msn.com
--
Regards,
Tom Ogilvy
It would be easier to convert the contract code number being looked up to a number to match the list
a1: '012345678
=vlookup(A1*1,sheet2!A:B,2,False)
so you multiply the string '012345678 by 1 to get 12345678 as a number.
If this isn't clear, then contact me at
twogilvy@msn.com
with a simple sample file
--
Regards,
Tom Ogilvy
---------- FOLLOW-UP ----------
QUESTION: Hi again Tom.
I just sent you the formula I am intending to use.
an example of the format of the lookup table is below
Scheme Scheme
01 to 20 21 to 40 Complete Code Complete Code
12011110 212011110
12011120 212011120
12011130 212011130
12011140 212011140
12011150 212011150
12011160 212011160 12011210 212011210
12011220 212011220
12011230 212011230
12011240 212011240
12011250 212011250
all the codes in column 1 should have a 0 in front so they can be found by the workbook which contains numbers where those beginning with a zero have been formatted with a ' as the 1st character (so they display the full 9 digit number) as follows
012011110 xXx Not Found xXx
012011120 xXx Not Found xXx
012011130 xXx Not Found xXx
012011140 xXx Not Found xXx
012011150 xXx Not Found xXx
012011111 xXx Not Found xXx
074025116 xXx Not Found xXx
074025116 xXx Not Found xXx
074025116 xXx Not Found xXx
074025115 xXx Not Found xXx
Hope this makes sense
ralph
thanks again
ANSWER: ralph,
see the VBA code I sent.
--
Regards,
Tom Ogilvy
---------- FOLLOW-UP ----------
QUESTION: Thanks for the macro Tom it did just what I wanted. Initially it stubled over the "End If" statement so I just removed it and it worked fine - thanks again
Answer Ralph,
Perhaps the code got mangled in the email.
Sub ModifyData()
Dim r as Range, cell as Range
set r = Selection.SpecialCells(xlconstants,xlNumbers)
for each cell in r
if len(cell) < 9 then
cell.Value = "'" & format(cell,"000000000")
end if
next
End Sub
Above tested and worked fine/worked as expected for me. The "IF then/End if" is written as a block and all lines are required. It could also be written as a single line IF statement and would not need an "End if"
Sub ModifyData()
Dim r as Range, cell as Range
set r = Selection.SpecialCells(xlconstants,xlNumbers)
for each cell in r
if len(cell) < 9 then cell.Value = "'" & format(cell,"000000000")
next
End Sub
or
Sub ModifyData()
Dim r as Range, cell as Range
set r = Selection.SpecialCells(xlconstants,xlNumbers)
for each cell in r
' next two lines are a single command because of the
' line continuation characters (space and underscore)
if len(cell) < 9 then _
cell.Value = "'" & format(cell,"000000000")
next
End Sub
So maybe the original code got mangled and the action part of the if statement ended up on the same line as the if condition then part
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
