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>

 

[Desu Nota from Columbus]

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

=mid(A1,3,3)

 

[T. Valko]

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

Note that the values being returned are TEXT strings and are not numeric numbers even though they may look like numbers.

One way to convert them to numeric numbers is:

=--MID(...)

 

[Desu Nota from Columbus]

Note that the values being returned are TEXT strings and are not numeric numbers even though they may look like numbers.

One way to convert them to numeric numbers is:

=--MID(...)

That is a useful tip that I did not know. Thanks Biff.

 

[T. Valko]

That is a useful tip that I did not know. Thanks Biff.

Some other functions that do the same thing, return TEXT values:

TRIM
MID
LEFT
RIGHT
SUBSTITUTE
REPLACE