lookup text within string of text???

I need to look up "certain critera" within a string of characters, then 
return that "certain criterea" to a new column. 
Some examples of a strings of characters may look like these:
K5J091509001
Sample PO#S881009
K55sample PO
CarrieRJR
TJ5
My "Certain Critera" I have listed on another sheet, named "REP ID"
K5J
S88
K55
RJR
TJ5
How do I pull out the 3 characters of "Certain Criterea" from the string of 
text and copy or enter it into a new column?

It's never consistant.  This is the problem I'm having! Sales Reps enter 
their 3 character id with a bunch of other junk as a reference.  I just need 
their 3 letter Id pulled out to do a vlookup with that information.

like RepID K5J could be nested anywhere within a string of text
12/10/09K5J PO#
K5J121009001
121009001K5J
Sample PO#K5J
KenREPIDK5J1210

and I have 100's of REP ID's listed on another sheet.

Anyone??? Please...... Thanks!!! :)
0
Utf
12/11/2009 3:29:01 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
1064 Views

Similar Articles

[PageSpeed] 11

Reply:

Similar Artilces:

validation list drop down box, how do I bring in text commands
I have a reference set in excel that is two columns. The first column lists functions in text (i.e. AVERAGE, COUNT, COUNTA, MAX, MIN....). The second column lists the corresponding function number (i.e. AVG = 1, COUNT =2....). I made a drop down box using data, validation, list so that the function numbers appear in a row. The row below I put in subtotal(grab command from drop down, data set to calc). I can't figure out how to return the TEXT from my columns above, rather than function number. I want my drop down to have AVERAGE, not 1. Hi Scott, Debra Dalgleish has pages ...

Delete text
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Can't delete marked text by pressing BACKSPACE Well, since the Mac keyboards I'm familiar with don't have a BACKSPACE key I'm not sure what you're pressing :-) but it's most likely a matter of going to: Word>Preferences> Edit, check the box for "Typing replaces selection". HTH |:>) Bob Jones [MVP] Office:Mac On 2/9/10 11:15 AM, in article 59bb2874.-1@webcrossing.JaKIaxP2ac0, "IO@officeformac.com" <IO@officeformac.com> wrote: > Version...

Is it possible to display text in charts?
I am creating graphs for results from a survey. Part of this is to graph each individual's response to every question. If a person did not answer a question, I would like "N/A" to appear in the data for the chart. I am not using formulas -- the numbers are typed directly into the spreadsheet. Is there any way to have the text "N/A" display in the chart data? Currently, if I enter text in the corresponding cell, it displays as "0" in the chart data. Thanks! Janine, You can try applying a custom format to those entries that are coming across as zero....

Search letters within Words from the List
I have the below code to search and copy the rows that matched some criteria.. I want to add one more search criteria to this code which checks for matching words.. something like below line.. (Sheets("Details").Range("B" & lngRow) = .Range("G2") Or Len(.Range("G2").Value) = 0) But the thing is the word entered may not be the full word Eg., Range("G2") = "it" Then it should check for words that contains "it".. Like profitless sit out Italy my code -------------------------------- lngLastR...

Entering wrapping text
I have two issues when entering text in an Excel cell. I format the text to wrap, and the row height increases to accept additional text as I type. Sometimes this stops happening, and I have to resize the row manually. How do I control this? Also, although the row size increases as I add text, it doesn't seem to decrease if I delete text. Is there any way to make this happen? When I format the text as "general" it displays correctly in the cell (although in the sample pane it displays as ##################). When I format the text as text it displays as ################### in bo...

Convert numbers from text format to number format
I have retrieved some data from external source and it put to my worksheet in one column numbers in text format and if I want to work with numbers (add, substract, etc.) I need it to convert to number format. I tried to select column and mark it as number format however it doesn´t work. Pls help me what to do, how to suceed. I have no chance to change the format in source. Juraj Hi Merlin68, > I have retrieved some data from external source and it put to my > worksheet in one column numbers in text format and if I want to work > with numbers (add, substract, etc.) I need i...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

change color in many text box
I have a form which takes information from a query. The information is orizontally aranged in the many text boxes . I would like to know how can i change the background color of the text box on the row by just doubleclicking on that particular row. Give me some suggestions please! ANY IDEEA, "florin" <florin_serban@hotmail.com> wrote in message news:ercv$ytcIHA.5164@TK2MSFTNGP03.phx.gbl... >I have a form which takes information from a query. > The information is orizontally aranged in the many text boxes . > I would like to know how can i change the background colo...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

VBA Code to Automatically move text into above cells?
I have (with the help of some of the very talented Excel MVP’s) nearly finished a to do list spread sheet! http://www.box.net/shared/ejzn141dux The worksheet works like this: Enter desired text in cell c3 and then use the drop down button in b3 to determine the position where the text is to be placed in the below list. If there is text already in that position all the text below is moved down one cell. This part of it works fine. Three lists can be managed this way – Home, Work and Other. However, I would like to be able to delete the text from a cell by using the back...

Hyperlinks and Auto Text
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) When inserting a hyperlink, the program replaces the highlighted text with {HYPERLINK <a href="http://">http://</a> etc.}. Remove the hyperlink and the text reappears. Also instead of displaying page numbers I just get {PAGE}. The correct page number prints as does the hyperlinked text. You've simply toggled display of the field codes. Press Option+F9 to toggle the codes off & display the results of the fields. HTH |:>) Bob Jones [MVP] Office:Mac On 3/29/10 11:21 AM, in article 59bb6259....

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Text disappears when word wrap is used
I am haveing a problem with Excel. Whenever word wrap is applied the text is not visable in the cell. It remains visable only in the bar above. I have already confirmed autofit and it reduces the column to 1 character wide. I tried detect and repair on the installation and nothing. I even upgraded from Excel 2k to Excel 2003 and no change. I would greatly appreciate help on this as it is really bugging me. Hi Mark, Widen the column, to what you want then use autofit on the row(s) not on the column. If you have merged cells involved you will have to adjust the row height other tha...

insert text from file
I am not in compatibility mode, but this is not an option as it is grayed out. Any suggestion. Is your cursor in the document where you want the insertion to go? On Jan 14, 10:20=A0am, Tammy O <Tammy O...@discussions.microsoft.com> wrote: > I am not in compatibility mode, but this is not an option as it is grayed > out. =A0Any suggestion. ...

Message text formatting
Hello, I am using Outlook 2007 as part of Office 2007 on XPSP2. When I send a message the formatting is fine. However, when I receive a reply, my original message has extra "line breaks" or carriage returns in it. Is there any way to prevent this? I already have Outlook set to remove extra line breaks. -- Thanks, Bob "BBran" <bbran@nowhere.com> wrote in message news:e21bDK%23uKHA.3368@TK2MSFTNGP06.phx.gbl... > I am using Outlook 2007 as part of Office 2007 on XPSP2. When I send a > message the formatting is > fine. However, when ...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

lining up text
I have two columns of text on a page. When I insert a text box within the text box to highlight a quote, the text in the original text box no longer lines up comparing the left and right columns. I've tried all sorts of tweaks to the text box layout to change this but nothing works. I'd appreciate any suggestions. Thank you. Does the text box have two columns or do you have two text boxes? If it is the former try a ctrl+shift+enter at the end of the first column, does this even it out? -- Mary Sauer http://msauer.mvps.org/ "Jeff" <Jeff@discussions.microsoft.com&g...

Attachments or Texts was missing after I click Send
I had encountered the above recently (a number of times) when I try to send a mail to oversea. I didn't notice it until my friend told me about it. Any possible solution? Thanks. ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

Lookup #4
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right? Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"? Mine is not working very well. Maybe my syntax is wrong. Any ideas? TIA Is there anyway you can st...

Creating String Property through CRM SDK
I need to create a String Property using the following VB.Net code: Try ' Set up the CRM Service. Dim service As New crmserver.CrmService() service.Credentials = System.Net.CredentialCache.DefaultCredentials ' Create the String Property object. Dim sm As New crmserver.StringProperty ' Create the properties of the String Property object. sm.Name = "MyString01" sm.Value = "Custom Text 001" ' Create the string property in Microsoft CRM. ...