I need help with a lookup function. I have an excel spreedsheet with 5

columns. , Clerk1, Clerk2....clerk5

The clerk fields contain text & numeric values (assignment codes). This is

a general field where any text can be inputted with the numeric value. I

need a formula to search all the columns, find a particular value (ie

"15.06") and identify which clerk is working on that assignment. In this

case, I would need the formula's value to equal "Clerk2" if I was looking up

"15.06". The columns are not sorted & contain a variety/combination of text

and numbers in each cell. All the numbers will have the decimal followed by

two digits.

Col A Col B

Row1 Clerk1 Clerk2

Row2 12.07 for JS 63.07, 15.06 due TR

Row3 899.07 & 701.07 for RO 229.07-231.07

row50 23.07 TR for RO 19.07 & 11.07

columns. , Clerk1, Clerk2....clerk5

The clerk fields contain text & numeric values (assignment codes). This is

a general field where any text can be inputted with the numeric value. I

need a formula to search all the columns, find a particular value (ie

"15.06") and identify which clerk is working on that assignment. In this

case, I would need the formula's value to equal "Clerk2" if I was looking up

"15.06". The columns are not sorted & contain a variety/combination of text

and numbers in each cell. All the numbers will have the decimal followed by

two digits.

Col A Col B

Row1 Clerk1 Clerk2

Row2 12.07 for JS 63.07, 15.06 due TR

Row3 899.07 & 701.07 for RO 229.07-231.07

row50 23.07 TR for RO 19.07 & 11.07

- Find matching number in two columns
- Find row number of text string in a range of cells
- Finding matching numbers in two columns
- Find Numbers In Text Strings
- Finding The First Number In A String
- Finding Numerical Value in Text String / Concatenate Based on Value
- VBA find phone number in string, add symbols
- Find position of a number in a string
- Help !, to find last 'text' date in text string
- Find position of a number in a string
- Find key words in text string
- Prob matching numbers in a column using VB.
- The VLOOKUP and HLOOKUP functions fail to find a number in a list in ...
- LOOKUP OR INDEX/MATCH TO FIND NUMBER?
- Find a number in Column A matching Column B & move # in C matching Column B
- Sum values in text strings across multiple cells
- Conditional Extraction Of Numbers In Alphanumeric Strings
- Find last number in a string?
- Convert a numeric number in text format in another cell?
- Find specific keywords in a string and return them in respective column.
- I need to find odd numbers in a column
- Find Duplicate Number in a column
- Loop and increment number in text string based in selection in user form
- Make sure there are no numbers in a string

My first time in this great forum and I have already got lots of resources from the site.

I want to loop through ID Column and find the matching number in Record No.column, and hightlight the entire row when match is found. The code I am running can only work for one cell at a time, but I want to run one time.

Sub FindAMatch() Dim AMatch As Range 'Matching cell in column A Dim CurSel As Range 'Current selected (active) cell - This should be "I" 'Column "I" The one you should select to Find Match in "Column A" Set CurSel = ActiveCell Set AMatch = Columns(1).Find(Selection.Value, after:=[a1], LookIn:=xlValues, lookat:=xlWhole) If AMatch Is Nothing Then Beep Else 'AMatch.Select AMatch.EntireRow.Interior.ColorIndex = 4 End If End Sub

I am trying to do the following

I have a set of variable names (i.e., text strings), which are embedded amongst other text in a large number of rows (i.e., in a range). I need to find the row number in the range where each variable name occurs.

To illustrate

I have might have two variables, called "Variable 1" and "Variable 2".

The range of rows with text might look like this:

random text random text

random text random text

random textVariable 1

[blank row]

random text random text random text

random text random text

[blank row]

[blank row]

random text random text

random Variable2text random text

In this range, Variable 1 occurs in row number 3, and Variable 2 occurs in row number 10 (but in other instances my variables could be in other rows) (also note that the range is a single column).

What formula can I use to pull out the row number information for each of my variables? I have tried to use a combination of "find" and "match" functions, but suspect I may need to enter these into an array formula of some kind? Any help on this would be great.

Regards,

Steven

eg Width Thick

100 38

100 50

100 75

150 38 etc

In another column are 8 sets of similar figures, and I want to find if any of them match the figures in the 2 columns ie

100 45 would not be a match, but 100 50 would be. I have used a very complicated method to achieve this, but would like to use something like lookup or match to do it in one formula. I tried concatenating the values but did not know how to achieve it that way.

No sets of the figures incolumn1/2 are the same, but the same numbers repeat in invididual columns

Cheers

Barry

Cell A1: abc 1234 def ghi

Cell A2: xxxx aa b 245 qqqqq

Cell A3: abcdefg hij kl mnopqr s

etc.

Is there an excel formula or combination of formulas I can use to identify:

(1) whether any given text string (such as those above) include numbers, and

(2) what the first number (which could contain 1-4 digits) contained in the text string is?

Thanks in advance!

- Steve

I am a major noob to all of this and I hope this question isn't one that has been asked multiple times already. I searched through these forums, but was unable to find an answer.

We are trying to make either a macro or a function that will look through the contents of a cell, find the first NUMBER and then paste the results to another column. Below is an example of what may be in cell A1:

#BC7K,03/30/2007,0.00636,0.0069,0.00614,0.0062,0.0,0

We want only to find the FIRST NUMBER in this string, so the result should be 7. Any help you could give us on this would be greatly appreciated! Thanks in advance!

I have a list of codes (sample AA11 through AA91, AB11 through AB91, and so on). I want to look at the third character of the code which is always a number. If the number is equal to 1, I want to concatenate the first two characters of the code with the information in cell AD1, if the third character of the code is not equal to 1, I want to concatenate the fist two characters with the informaiton in cell AC1. Below is the formula I started with; it works as long as there is not a 1 in the code...

=IF(ISNUMBER(FIND(1,A85,3)),CONCATENATE(MID(A85,1,2),AD$1),CONCATENATE(MID(A85,1,2),AC$1))

Thanks for your help!

Anyone want to tackle this VBA problem? For column A:

1-Find a sereis of 10 numbers in a string of text of other characters,symbols and #'s. (example blah&697_9*akb1234567891) where 1234567891 is a string of 10 #'s.

2-Insert symbols within the text string so that the series of 10 numbers looks like 123.456.7891 (the text string would then look like blah&697_9*akb123.456.7891)

Thanks in advance!

postcodes that will obviously have different digits.

I want to go from using many FIND functions e.g.,

=FIND(0,A1), then =FIND(1,A2) etc...

to

=FIND(0-9,A1)

what text would I need to put in to replace the "0-9"?

Thanks

I am trying to determine how many days ago (from TODAY or DATE) was a

entry into a 'text' cell made, but I don't know to find the end of the text and

search backward for the last date.

In the cell are multiple text entries preceeded by the date of the entry, with the most recent entry appended to the end of the cells current text string.

The typical text of the cell looks like below (note: the date entry is

always shown as: ", mm/dd/yyyy:")

lots of text,,more text,, , 12/28/2005: lots of text,,more text,, r

, 12/29/2005: Sent e-mail lots of text,,more text,, r , 12/30/2005:

lots of text,,more text,, r , 1/17/2006: lots of text,,more text,, ,

1/19/2006: lots of text,,more text,, , 1/27/2006: lots of

text,,more text,, , 1/30/2006: lots of text,,more text,, ,

3/1/2006: lots of text,,more text,, , 3/1/2006: lots of text,,more

text, text end.

I think the pseudo code approach would look similiar to:

dim todaydate as date

dim founddate as ??

dim count as integer

todaydate = date 'get and save todays date

range(the_text_cell).value.select

with selection

.find ( here is where I am lost)

[probably need something here to convert the found date 'text' value to date type]

count = todaydate - founddate

Thanks for any help you can provide :-)

postcodes that will obviously have different digits.

I want to go from using many FIND functions e.g.,

=FIND(0,A1), then =FIND(1,A2) etc...

to

=FIND(0-9,A1)

what text would I need to put in to replace the "0-9"?

Thanks

A1 = "Patient was referred to Mercy Hospital on Monday"

A2 = "Patient was referred to Dr. Ford"

A3 = "Patient was referred for sleep studies"

A4 = "Patient was referred to Mercy Medical"

I need to use this input to create standardized output for cells in my spreadsheet. I have tried to use “Search” and “Match” commands to evaluate a text string against a series of key words. When a match is found, I would like to output the text in a cell adjacent to that containing the matching key-word.

Key words:

B1 = "Mercy Hosp"

B2 = "Ford"

B3 = "sleep"

Output for "Location of Service":

C1 = "Mercy Hospital"

C2 = "Mercy Hospital"

C3 = "Mercy Hospital"

C4 = "" (e.g ...no match)

Please let me know if you have recommendations.

Thanks in advance!

VB:The script is supposed to check column D (Lift truck # column) and find any values that match and then subtract the log times of the left adjacent C column cells next to the matching numbers in D column and print the diffennce in column G beside the right bottom most matching number in D.Range) Dim rng As Range Dim c As Range Set rng = Columns(4) If Intersect(Target, rng) Is Nothing Or Target.Count > 1 Then Exit Sub Target.Offset(0, -1) = Time Target.Offset(0, -2) = Date Set c = rng.Find(Target, After:=Target, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious) If Not c Is Nothing And c.Address Target.Address Then Target.Offset(0, 3) = _ Target.Offset(0, -1) - c.Offset(0, -1) End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

As you can see the script is seeing "1450" as "50" and displaying the difference when the two numbers don't actually match. The script is seing the "50" that is in the "1450".

I am sure there is a quick and easy solution, but I an not experienced enough to know what it is..

Thanks for the help!

Describes an issue that VLOOKUP and HLOOKUP functions fail to find number in list in Excel. ... When you use a VLOOKUP formula or HLOOKUP formula to locate a calculated LOOKUP value, the ...

Basically if it finds that number in that table then "YES", otherwise "NO".

This some Example data, but the table is much larger. Also, note, that the

only number that can be found more than once is 0, which is not searched

anyways so we don't care about 0.

I14=1

I15=2

I16=22

I17=34

I18=41

2 1 3 0 0 0

4 6 7 5 0 0

8 9 14 12 11 13

20 21 18 19 17 16

28 27 32 26 23 25

36 41 35 38 33 37

J14=YES

J15=YES

J16=NO

J17=NO

J18=YES

Any help would be appreciated.

For example

Cell A1 = 18K,18Mo,18Co

Cell B1 = 2Na,2Si,2K,2Mg,2Fe,2Ca,2Al

I would like to have a formula in C1 that will return 68 (the sum of all the values in these two strings)

The text strings in each cell can be variable length and the numbers can be one, two or three digit numbers but will always be proceeded by a comma (except for the first occurance).

Any help would be greatly appreciated.

Thanks

I have a column with product packaging sizes with different syntaxes, such as:

"2 x 5 kg"

"200 l (212 kg)"

"1000 l (1400 kg)"

"5 kg"

"20 l (20,54 kg)"

As you can see the syntax varies a lot! Now I need a formula to extract certain numbers for different calculations. E.g. if the string has a letter "x" then the number(s) in front of it would be extracted. Or if the string has the letter "l" or "kg" the numbers in front of them (including decimals) would be extracted while discarding the rest. Is this even possible? Personally, I would change the whole system and break the strings across a number of columns but unfortunately it is not up to me...

with say first 4 digits and want it to tell me the next number in the

sequence, then how do i do it in excel if at all?

i.e.

if a list of numbers such as 1234/1, 1234/2, 1234/3....i want it to tell me

the next in the sequence is 1234/4..

any ideas?

its a pity you cant just think these thoughts in "help"...

while using ms excel 2003 i am unable to convert any numeric number in text

fromat in another cell or worksheet. what i want to do, that can be explained

as follows: " in cell a1 i typed Rs. 1000" and i want this figure to

converted in cell c45 as " Rupees one thousnad". could you please help me out

on this and the same thing as vice versa.

thanks,

abhishek

I have a following requirement.

I want to search for three words in a string, if any of the keywords is found first in the string, then that keyword should be placed in the cell left of the cell in which string is there.

For example:

Cell B1 contains "System PR1 is connected to PC1 with an interface at PB1."

No I want to search for any of the following keywords:

PR1 or PC1 or PB1.

Since it will find PR1 first in the string, so in cell A1, it should return PR1, and should omit searching remaining keywords, since it has found the first keyword that I am looking for.

Let us suppose if PR1 is not in the string then it should return PC1 in the cell A1 since it is a second keyword I am looking for in the string if available, if PC1 is also not in the string, then it should search for PB1 in the string and if it finds PB1 in the string then it should return PB1 in the cell A1.

If none of the keywords are part of the string, then it should return whatever I want to return like "Null" or "Not Found".

I tried the Find command but it is not working in the above scenario, and does not start finding for the second keyword if the first keyword is not found.

Any help would be of great help for omitting the manual work I am performing in Excel.

Thank You,

Saurabh.

or all odd numbers in a column. IIs there a simple function for doing this?

Hi All,

I'm trying to find duplicate number in column A that has the same match with Product, Part, and Id. Somehow this formula doesn't work. Your input is greatly appreciate.