Hi,
I'm trying to do a vlookup in my macro. However spreadsheet one has 6
numbers and the other sheet adds # and letters after the 6 number. Is thier
anyway to get rid of the # and letters after the sixth.
Example:
834543
834543R7

742351
74235112

Thanks,
DJ

"Duncan_J" wrote in message
...
Hi,
I'm trying to do a vlookup in my macro. However spreadsheet one has 6
numbers and the other sheet adds # and letters after the 6 number. Is
thier
anyway to get rid of the # and letters after the sixth.
Example:
834543
834543R7

742351
74235112

Thanks,
DJ

If 834543R7 is in cell A1, you can enter =LEFT(A1,6) in column B and drag
the formula downwards. It will return 834543
Do a vlookup on column B

/Fredrik

To clean the data there are a couple of things you want to do. Before you
start make a copy of the column you are about to play with in case something
goes wrong. Do a find and replace on the # symbol to remove all instances of
that. This can also be done with a formula but it is a lot trickier. Let me
know if you need that. Now you can deal with the remaining numbers. You can
split up the numbers using the Text to Columns function on the data menu.
Just select Fixed Length and place a divider after the 6th digit. You can
also do this with formulas if you want but again it is a lot trickier...

HTH

"Duncan_J" wrote:

Hi,
I'm trying to do a vlookup in my macro. However spreadsheet one has 6
numbers and the other sheet adds # and letters after the 6 number. Is thier
anyway to get rid of the # and letters after the sixth.
Example:
834543
834543R7

742351
74235112

Thanks,
DJ

Hi,
In your macro you can insert a column to the right of the look up column in
the second sheet. Then you can run a loop that would populate the new column
with the first six characters of the column to its left:

CELLS(LoopVariable,NewColumnNumber)=LEFT(CELLS(Loo pVariable,NewColumnNumber-1),6)

After the loop you'll need to do a Copy Paste Special... Values to get
rid of the formulas. Then you'll probably need to convert the column to a
number format. I use the text to columns function in my macros.

If you need the specific VB code for any of these steps, let me know.

"Duncan_J" wrote:

Hi,
I'm trying to do a vlookup in my macro. However spreadsheet one has 6
numbers and the other sheet adds # and letters after the 6 number. Is thier
anyway to get rid of the # and letters after the sixth.
Example:
834543
834543R7

742351
74235112

Thanks,
DJ

Sorry I just noticed that you just want the first six digits. Here is an easy
formula

=value(left(A1, 6))

Where the number you want to look up is in cell A1... :) I thought your #
was somewhere anywhere in the number...front, back, middle...

"Duncan_J" wrote:

Hi,
I'm trying to do a vlookup in my macro. However spreadsheet one has 6
numbers and the other sheet adds # and letters after the 6 number. Is thier
anyway to get rid of the # and letters after the sixth.
Example:
834543
834543R7

742351
74235112

Thanks,
DJ

Thanks Guys! That did it...

"Duncan_J" wrote:

Hi,
I'm trying to do a vlookup in my macro. However spreadsheet one has 6
numbers and the other sheet adds # and letters after the 6 number. Is thier
anyway to get rid of the # and letters after the sixth.
Example:
834543
834543R7

742351
74235112

Thanks,
DJ

That formula returns a string, not a number. If you are using it for lookups
it won't work if the values being looked up are numbers... The value function
may be required to change the string into a number... Depending what is being
looked up...

"Fredrik Wahlgren" wrote:


"Duncan_J" wrote in message
...
Hi,
I'm trying to do a vlookup in my macro. However spreadsheet one has 6
numbers and the other sheet adds # and letters after the 6 number. Is
thier
anyway to get rid of the # and letters after the sixth.
Example:
834543
834543R7

742351
74235112

Thanks,
DJ

If 834543R7 is in cell A1, you can enter =LEFT(A1,6) in column B and drag
the formula downwards. It will return 834543
Do a vlookup on column B

/Fredrik