Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

lookup or find matching number in text string

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


Post your answer or comment

comments powered by Disqus
Hello Everyone,
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


Hi,

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

I have 2 columns - one represents width the other thickness
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

I have several cells in a column that look something like this:

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

Hello all!

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!

Is there a way to look at one specifc character in a string of text?

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!

Hi there-

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!

How do I find the first occurrence of a number in a string? I have a list of
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

Newbie needs help, finding last 'text' date in text string !

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 :-)

How do I find the first occurrence of a number in a string? I have a list of
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 have series of cells in a spread sheet that contain free text:

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!

I am having a problem with this script.


	VB:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

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!

The VLOOKUP and HLOOKUP functions fail to find a number in a list in ...

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 ...

I was looking for a formula that looks up a number in a large table.
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.

Find a number in column A that matches a number in column B. Take the number in column C next to the matching number in B and post it in column E on the same line as the number in column A

I'd like to use a formula to sum the values found in text strings across multiple cells.

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

Hi!

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...

If i have a table of numbers in an array and want to find a number starting
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"...

HI,

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

Hi All,

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.

I have a large excel spreadsheet that I need to find either all even numbers
or all odd numbers in a column. IIs there a simple function for doing this?

IF(SUMPRODUCT(--($A$2:$A$251=A2),--($D$2:$D$251=D2),--($E$2:$E$251=E2),--($C$2:$C$251=C2))=1,"","Duplicate")

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.

How would someone go about looping this selection. If part A has 2 parts and part B has 4 parts and both the weight and length need to be recorded. Example: if part B is selected I need question 1 to read ” weight of part #[1]” so in this case it would loop 4 times and each time the number in the brackets would increase up to #4 in this case.

I know this one is probably very simple but what code can I use to make sure there are no numbers in a string?


No luck finding an answer? You could always try Google.