# Lookup For A String Of Numbers

Oct 20, 2008I have a list of data, 7 coumns x 4000 rows with integers in them. b2:h4173

I would like to write a forumula(e) which will tell me which 3 integers appear in the same row most often.

I have a list of data, 7 coumns x 4000 rows with integers in them. b2:h4173

I would like to write a forumula(e) which will tell me which 3 integers appear in the same row most often.

ADVERTISEMENT

I have a field that contains the following: 012100002030

I need a formula that will tell me whether or not the string contains a number higher than 2 or whether or not it contains more than one 2.

Examples:

001000002011

111111000022

401110000000

the first loan would not meet the criteria as it contains no number higher than 2 and only contains one 2.

the second loan would meet the criteria as it contains two 2's.

the 3rd loan would meet the criteria as it contains a number higher than 2.

I need to do a lookup in a column for part of a string and return the adjacent value in a different column. Works fine using VLOOKUP as seen in the following example except that the return value is being truncated.

Lookup range in column A:

A001,A002

A003,A004

A005,A006

I need to find A001 and return the value in column I of the same row. I am currently using {VLOOKUP("A001", LEFT(A1:I500, 4), 9, 0)} This does work entered as an array formula but it is only returning the left 4 characters of the column I value. I need to lookup the left 4 characters in the lookup range but return all the characters in the return range.

mockup.xlsxStarting out with a 9 by 11 grid.

Column A will have times that need to be referenced in a lookup. (Ex Sat 8:00am)

Row 1 will have basball field codes reference with a lookup. (Example Field 4

Cells B2:I11 have distinct codes or possibly nothing. (An example would be: Tier 1 A1-A3)

These codes will move around from time to time. They will always remain distinct within the grid, but could appear in any of the cells from B2:I11.

I have 2 columns I want to populate with values from the grid.

First is Fields. I want the first cell to populate with the baseball field found in row 1 of which the code Tier1 A1-A3 falls under.

For example if this season Tier1 A1-A3 is found in the column under Field 4, then I need to return Field 4 as a result.

Second is the time. I want the time to be populated based on what row the Tier1 A1-A3 code falls in.

For example if Tier1 A1-A3 is in the Sat 8:00am row, then I need that value returned.

In short, I need to search the grid and find a given value. I then need to use a lookup (or other tool) to return the first value in that row. And secondly via lookup return the first value in that column.

edit: added spreadsheet example. First sheet is grid in question, 2nd sheet is a typical schedule.

The first Team (Scared Hitless) is A1, the team they play first is Sands Duel Fuel (A3) They play at 11:00am on Field TR3.

You will see on the grid under TR3 and Sat 11:00 the code Tier 4 A1v3. This code is what I want to search and correlate into times on the schedule rather than doing the data entry by hand.

how to figure it out this lookup problem (lookup using partial string of match)...

View 5 Replies View RelatedI need to look within column A (which contains text strings), match label in column c (which contains text for label), and return result within column B (titled category label).

Essentially, I want to label my data based upon a specific element found within column A with a label list in column C.

I have attached an excel file that shows the current problem. Nested if(isnumber(search( statements can perform this, but I yield too many arguments error after three labels?

Isn't there a way to do this with Vlookup or Hlookup?

I have account information with 4-digit customer location identifiers beginning at character 9 and ending at character 12:

3600820040010500050006080000360082004004050005000503000036008200400105000500068900003600820040050500050006880000360082004001050005000402000036008200400105000500052320003600820040030500050005271000

No matter what changes in this string, these four digits (9, 10, 11 & 12) will always be in the same position.

What I would like to do is (I think) use the FIND funtion and then perform a VLOOKUP against an already defined table/range which identifies the actual location in the destination cell. Will FIND be the easiest for me to do this our should I use something else?

The what to interpret characters 9-12 (using 3600820040030500050005271000 as an example):

3600820040030500050005271000

4003 is the location information which consistantly breaks down:

4 = Region Name

003 = Office / City (001 thru 024)

There are 7 possible regions with 24 possible Offices/Cities. I want to believe I can make this lookup happen using VLOOKUP tables that break them down - but I just don't have the brainpower to identify those 4 digits and then break them out for a look up.

I have an endless list of information that I need to turn into a text string, lookup with a validation on the end.

The easiest way is to show you so I've attached the info and what result I want.

Attached File : Test.xlsx

Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:

Capture.PNG

- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on.

- I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.

I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?

I have a spreadsheet!

I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).

What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.

Example:

Sheet 1 - Films

How To Apply Bridal Makeup

How To Fight A Donkey

How To Write Excel Formulae

Sheet 2 - Tags

Channel / Tag / Keywords

Tech - Microsoft - windows vista xp word excel

Tech - Computers - internet pc

Tech - MP3 Players - iPod Zune

So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.

I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number.

Col A Col B

20567 3M Electrical, Inc.

I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both...

Col A Col B

20567 3M Electrical, Inc.

21789 3M Tape Division

Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?

I want to make a lookup on a string of words from one of my worksheets and I also whant to make a hyper link so that when I click on the lookup command I should pop to the other worksheet were my database is.

View 2 Replies View RelatedI maintain a spreadsheet to track monthly sales of a few thousand items (see attached sample data). I'd like to have a formula that would sum only the last 12 months in the range of data. It would need to ignore all of the data before and the blank cells after the 12 months.

It's difficult to update the range each month for all of the products.

I have data like follwing strings.

WAY-308312-1

WAY-521340-1

233-6767-676-6

First two strings are correct. If I get number like in third string first part of string then we have to move row to some other sheet.

The first part of string should have text like WAY-123-13-1.

I used Val function it checking first charector only. It the string is like 2A1A-243-233-2 the it is not checking.

I would like to extract the digits below in red. The variables are the digits in all of the sections vary from a minimum of one digit to the maximum of three digits.

111.222.333.444

I need a formula to extract the numbers from data in a cell:

Examples of data in cells:

G 622 ENTERTAINMENT ( I need to extract 622 )

D 6129 TOYS ( I need to extract 6129)

C 1039 Toddler TOYS ( I need to extract 1039 )

R 05 VEHICLES (I need to extract 5 )

All the cells have spaces before an alphabetical identifier, then a space ( it could be more than one in some cases ), then a number ( between 1 and 4 digits ), then a space and a description.

The table below is a portion of a calendar to track vacation and sick time. What I am trying to do is add all the hours of vacation ("V") and sick ("E") time that an employee has used in a cell at the end of each row which represents a month. So there are 31 cells that I need to look in and if it contains V... sum in the vacation cell. There may be a space after the letters and there may not be. They can also start with a decimal.

month

1

2

3

4

5

Vacation

Sick

January

E8

V8

V.5

V6.25

E .5

Sum of V (should be 14.25)

Sum of E

I am struggling with a problem which involves a column with both the text and nos in that. I want to calculate the following formula.

Total = the Sumproduct of the nos in the description*Length*Width*Height.

In the description column,

1) The nos will be varying i.e x*x or x*x*x or X*x*x*x*.

2) After the nos there will not be any data.

Example table:

Work Description

Length

Width

Height

Total

Parapet wall 2x4x8

12

2

4

6144

[Code] .......

Is there a way to achieve this, what is the formula for this ??

I am looking for formulae to identify the first 20 non-zero numbers from a string of alphanumerical and non alphanumeric characters

EG If column A any data such as:

123

0123

01(2)3

0.123

123abc

0123abc

01(2)3abc

0.123abc

a1b2c3

1a2b3c

Then the corresponding row for column B would return 123

Using help from my maths Prof and some IT bods, i have so-far come up with the array-formula:

=1*MID(A7,MATCH(FALSE,ISERROR(1/MID(A7,ROW(1:100),1)),0),20)

assuming that the total string is not more than 100 characters.

is there a way to have the number formatting in Text() variable? For instance, I want to reference a cell that can change between $1.00 and $10,000,000.00.

Let's say that in A3, the number turns out to be $10,000.00.

When I put in text(a3,"$0.00") it spits out $10000.00. But if I go the other route and go text(a3,"$000,000,000) it displays "$000,010,000.00".

I have not managed to find exactly what I am looking for so far..

I have cells with a mix of numbers and strings and I would like to extract certain numbers only.

I.E.

123a bcd8

acbd 1234e

I would like to only extract the first instance of numbers - so in example 1 - 123 and in example 2 - 1234. I have found various methods of extracting all numbers such as:

Function ExtractNums(r As String) As String

'Creates and returns a reference for a regular expression object

With CreateObject("vbscript.regexp")

'Sets the pattern to be non-digits

.Pattern = "D"

'We want to find all matching non-digits

.Global = True

'Replace all non-digits with an empty string and return this value for the function

ExtractNums = .Replace(r, "")

End With

End Function

I am trying to find numbers from a string. I have for example words "EUR Fwd 9x12" and "Eur Fwd 11x15" And i want to write a function that reads the first number from a string if there is only one number before "x" or two numbers if there are 2 numbers. So I have tried to build following function:

Function NumbersInString(Word As String) As Integer

Dim i As Integer

Dim FirstNumberInString As Integer, SecondNumberInString As Integer

For i = 1 To Len(Word)

If IsNumeric(Mid(Word, i, 1)) Then

FirstNumberInString = Mid(Word, i, 1)

If IsNumeric(Mid(Word, i + 1, 1)) = False Then

Exit Function

Else

SecondNumberInString = Mid(Word, i + 1, 1)

End If

End If

Next

NumbersInString = FirstNumberInString & SecondNumberInString

End Function

I have a worksheet with following values:

A

B

C

1

Shorts

75

[Code]...

Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".

What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.

Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.

I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.

A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.

Example:

xxxx1

yyy3yyyy1

xyzxx1yxz4xzx1

xxx2xxx2xxx2xxx2

What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:

xxxx1

yyy3 and yyyy1

xyzxx1 and yxz4 and xzx1

... and so on

Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?

I have textstrings which have numbers between questionmarks included which I want to have deleted.

Here is an example : This ?112? is text 12 and this must ?9? be deleted.

I want to have it as : This is text 12 and this must be deleted.

Is this possible with the 'Substitute' function or is there another way to do this?

In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.

for example

in column A i could have "Hello how are you 01237232 I am fine"

I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.

The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.

How I an achieve this?

I have a list (SIC Codes) and I want to remove the numbers. The numbers range from 2 to 8 deep. The list exists in column B and I want the new list in column C.

01 Agricultural Production Crops

011 Cash Grains

0111 Wheat

0112 Rice

0115 Corn

0116 Soybeans

0119 Cash grains, nec

011901 Pea and bean farms (legumes)

01190101 Bean (dry field and seed) farm

01190102 Cowpea farm

01190103 Lentil farm

01190104 Mustard seed farm

I need to add 2 to 6 zeros to the end of string of numbers beginning in A2:

01

011

0111

0112

011901

The end result, all numbers must be 8 characters long.

i want Counting duplicate numbers in a string for example Counting 2

22241278-------- Count(2)=4

I have several lines with text strings containting three numbers in each line. I need a code to extract all three numbers from each text string. The numbers can be placed on the following columns in each row.

View 2 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved