VLookup Help

[bergmc00]

Hi there,

quick followup, if I was to agree to columns to each other in a third column but the first column had some data that included some letters included along with the number I need identified, how would I set up that formula. For example, two columns listed below:

<TABLE style="WIDTH: 381pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=508 border=0><COLGROUP><COL style="WIDTH: 333pt; mso-width-source: userset; mso-width-alt: 16237" width=444><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD id=td_post_2719319 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 333pt; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=444 height=20>G_4397.</TD><TD class=xl94 style="BORDER-TOP: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; BORDER-RIGHT-COLOR: #ece9d8" width=64>4397</TD></TR></TBODY></TABLE>

 

[T. Valko]

Hi there,

quick followup, if I was to agree to columns to each other in a third column but the first column had some data that included some letters included along with the number I need identified, how would I set up that formula. For example, two columns listed below:

<TABLE style="WIDTH: 381pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=508 border=0><COLGROUP><COL style="WIDTH: 333pt; mso-width-source: userset; mso-width-alt: 16237" width=444><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD id=td_post_2719319 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 333pt; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=444 height=20>G_4397.</TD><TD class=xl94 style="BORDER-TOP: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #95b3d7 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; BORDER-RIGHT-COLOR: #ece9d8" width=64>4397</TD></TR></TBODY></TABLE>

Can you rephrase that.

It's not clear what you're asking.

 

[bergmc00]

I have two sets of data in two columns, I need to get rid of letters before and after the number in column one so that I can do a simple Vlookup on the data sets, how would I remove the letters from the data set in column 1 assuming that they are in the beginning and end of the number.

 

[T. Valko]

I have two sets of data in two columns, I need to get rid of letters before and after the number in column one so that I can do a simple Vlookup on the data sets, how would I remove the letters from the data set in column 1 assuming that they are in the beginning and end of the number.

Post SEVERAL representative samples.

Show us the "before" and "after" results that you expect.

 

[bergmc00]

<TABLE style="WIDTH: 507pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=676 border=0><COLGROUP><COL style="WIDTH: 333pt; mso-width-source: userset; mso-width-alt: 16237" width=444><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 333pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=444 height=20> C_580~Restaurant I want 580

</TD>

<TD id=td_post_2719319 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=72></TD><TD class=xl96 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #95b3d7 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=160></TD></TR></TBODY></TABLE>

 

[T. Valko]

<TABLE style="WIDTH: 507pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=676 border=0><COLGROUP><COL style="WIDTH: 333pt; mso-width-source: userset; mso-width-alt: 16237" width=444><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 333pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=444 height=20>C_580~Restaurant I want 580


</TD>


<TD id=td_post_2719319 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=72></TD><TD class=xl96 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #95b3d7 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=160></TD>
</TR></TBODY></TABLE>

We need to see SEVERAL examples so we can determine if there's a pattern to look for.

 

[bergmc00]

but thats the only number i need. thanks for your help.

 

[Desu Nota from Columbus]

are all the values in column 1 in the same format? See below

1. Same amount of characters (letters + symbols+spaces) before the numbers?
2. Same amount of numbers?


Ex:
aaaa1234bbbb In A1

cccc5678dddd In A2

eeee9876fffff In A3


If the answer to 1.) and 2.) is "yes" then you can use the =mid function of excel.
In another cell I chose b1 type:

=mid(A1,5,4) and this returns 1234 from cell A1
=mid(A2,5,4) and this returns 5678 from cell A2
=mid(A3,5,4) and this returns 9876 from cell A3



Explanation of the formula, =mid("cell of interest" A1, "number of characters until first important character reached" 5, "amount of important characters" 4)

aaaa1234bbbb --> in this example, A1 is cell of interest, "1" is the 5th character, and we want all 4 numbers; therefore, =mid(A1,5,4) = 1234

 

[bergmc00]

this is perfect, thanks.

 

[T. Valko]

but thats the only number i need. thanks for your help.

Ok...

A1 = C_580~Restaurant

This formula entered in B1:

=--MID(A1,3,3)

Returns 580