pulling certain characters from a 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?
-1
Utf
12/10/2009 7:20:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1758 Views

Similar Articles

[PageSpeed] 14

Hi,
=left(a1,3)

"SaraMack" wrote:

> 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?
0
Utf
12/10/2009 7:28:02 PM
Looks like you might have to use several formulas depending on the line/row 
you are on.
Assuming your data begins in A2 and is consistant as you listed it you could 
do something like this for each row and then copy/paste values into a new 
sheet:

K5J091509001	=LEFT(A2,3)
Sample PO#S881009	=MID(A3,SEARCH("#",A3,1)+1,3)
K55sample PO	=LEFT(A4,3)
CarrieRJR	=RIGHT(A5,3)
TJ5	=TRIM(A6)


Hope that helps.

Frank


"SaraMack" wrote:

> 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?
0
Utf
12/10/2009 7:36:02 PM
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.

Do you know what I mean?
"FrankWood" wrote:

> Looks like you might have to use several formulas depending on the line/row 
> you are on.
> Assuming your data begins in A2 and is consistant as you listed it you could 
> do something like this for each row and then copy/paste values into a new 
> sheet:
> 
> K5J091509001	=LEFT(A2,3)
> Sample PO#S881009	=MID(A3,SEARCH("#",A3,1)+1,3)
> K55sample PO	=LEFT(A4,3)
> CarrieRJR	=RIGHT(A5,3)
> TJ5	=TRIM(A6)
> 
> 
> Hope that helps.
> 
> Frank
> 
> 
> "SaraMack" wrote:
> 
> > 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?
0
Utf
12/10/2009 8:22:02 PM
Reply:

Similar Artilces:

?problems printing from certain browsers
On my laptop with Vista Home Premium I had not used any physical printers or their software, but did use a few print-to-pdf programs for printing to files. Recently I installed (a)software so it could access two (identical)Epson printers I have always used with my PC. All of the printing goes well as long as I print from a Windows Explorer file with Notebook-type apps, and probably Irfanview as well. But when I am online with IE7 or Firefox and click to print something from a webpage nothing works, not even my print-to-pdf anymore -- instead I get an error saying the browser...

Picts in text
When I receive picture or other e-mail from a friend it occasionally arrives as a "txt" file. Not all the time... Occasionally also, if he sends me a series of, say 5 or 6 picts I have just a box with the red x, but as I sroll farther down the page the pictures are present. thanks in advance, jem what email client does he use? the problem could be on his end or yours. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginn...

spanish characters
I have been searching for the answer and can't find it. Please help! I used to know how to include Spanish tildes and accent marks using=20 Microsoft Word, but now that I use the program on a Mac, I can't figure = out=20 how to do it.=20 Please enlighten me! Thanks. Tildes: Hold down the Option key, and then press the "n" key. Release the keys and then press the letter you want underneath the tilde. � Accent Marks: Hold down the Option key, and then press the "e" key. Release the keys and then press the letter you want underneath the accent mark. (This shou...

Adding a text area
Hi, I have a CFrameWnd derived class as an SDI application. I have not used application wizard but just created this class to do some sprite animation on. I have created the code for that, overriding the OnPaint etc but now I want to display a text area on the window in quesiton. Assuming I do my sprite animation on a certain part of the frame's client rectangle is there a way to display a text area on the window? It is just a simple text area with scrolling that I want. I thought CFrameWnd was ok for building user interfaces but every example of CEdit I see is on a dialog frame. Ca...

Off Topic: Pulling Hair Out
Want to have some fun? Search microsoft.public.pos on google groups for "hair." You'll get all of the posts about people pulling thier hair out. Hint: There's more than one page. ...

How do I populate a DataSet from a XML string?
I have a string containing valid XML. How do I populate a dataset based on the string, without reference to an XML schema or writing the XML to file and reading it back in again? Goldsworth_Systems wrote: > I have a string containing valid XML. > How do I populate a dataset based on the string, without reference to an XML > schema or writing the XML to file and reading it back in again? You need an XML schema I think that describes the structure of the XML, then it is no problem to read in the XML into an XmlDataDocument. But without a schema it is not possible to associate t...

Pull down menu
Hello, I am trying find a way to make a pull down menu in excel. I have a list of foods on one sheet in a workbook where all the nutrition information is stored per food type where the row indicates the food type and the columns indicate the nutrition info ie: Carbs, Protein, Fats, Calories etc. This sheet has roughly 300 or so food items listed in rows. How can I create a pull down menu in another cell on another worksheet as a method to select a food item from the food sheet? Any help is appreciated, Dave --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (...

How to Anchor Decimal in Text Box
Hello Colleagues, Is it possible to "anchor" decimals in a column of numbers in a text box so all of the decimals in the rows are in perfect alignment? For instance, consider the text below as the contents of a PowerPoint text box: $13.5 Volume (2.1) Mix 4.4 Diversions I seek to have the decimals in vertical alignment in the three rows. Using leading spaces doesn't work so well because with proportional fonts, the parenthesis are a different width than the numerals. Tabs are worse because some of the rows do not have the same number of characters befor...

2007
Hi! - I was hoping someone could help me please. Here's what I have. A worksheet that has several dates in a row. D1:T1, Of that range, I have conditional formatting set to highlight a date(s) either = to or within the next 7 days. I need to be able to pull the date next closest or equal to today. Can anyone help me out? Any help would be greatly appriecated. Shaun > I was hoping someone could help me please. Here's what I have. A > worksheet that has several dates in a row. D1:T1, Of that range, I > have conditional formatting set to highlight a date(s) either = to or...

word cannot be pulled up
Do I need a updated version of word. Cannot bring up any word files now. 99 version. If you are saying that you cannot double-click to open Word documents, see http://word.mvps.org/faqs/apperrors/reregisterword.htm. Note that there is no "Word 99"; you need to look at Help | About Word to find out which version you are actually using. -- Stefan Blom Microsoft Word MVP "rentals" <rentals@discussions.microsoft.com> wrote in message news:12565263-2B58-4C40-A0C3-192A933FF438@microsoft.com... > Do I need a updated version of word. Cannot br...

I Want a Warning Before Sending Email to Certain Contacts
Can someone tell me how to configure Outlook so that I will get a warning message before sending email to certain contacts? For example, I have certain contacts categorized as "adversary" or "client" and I would like Outlook to make me confirm that I do, in fact, want to send an email to anyone from those categories. The idea is to prevent myself (and colleagues) from accidentally sending an internal email to the wrong person because of Autocomplete, inadvertence, or some other mistake. Thanks in advance. Extra points for a firm-wide solution--something the s...

Formating Text in the Subject Line
I've received emails where the text is in red, italics, etc. Can anyone tell me how this is done? Thanks! Mitche It's a View with Automatic Formatting - simple formatting rules can be created using Tools, Organize or use the Define view dialog on the View menu to create complex rules. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: htt...

Word VBA String functions
Hi, I'm looking for a function that can parse a string in Word VBA. Specifically I want to break a directory path into separate parts so that I can create the directories/subdirectories. I couldn't find one in Word but I know the Find function in Excel can do this. 1. Can I use an Excel function in Word VBA? (I believe I may have done this sometime in the past but can't recall how.) 2. Does anyone know of a better function/method to parse within Word, or can you suggest a different/better approach? Thanks, JillE You can use the Split function - eg Dim sP...

in C# how do i transform an xml document with an xsl document when my xml document is a string and my xsl document is a string? the msdn examples only show how to do it with steams and files. in my ca
in C# how do i transform an xml document with an xsl document when my xml document is a string and my xsl document is a string? the msdn examples only show how to do it with steams and files. in my case i have everything in string Daniel wrote: > in C# how do i transform an xml document with an xsl document when my xml > document is a string and my xsl document is a string? the msdn examples only > show how to do it with steams and files. in my case i have everything in > string Use a StringReader - if you want to output it to a string, use StringWriter. ...

Character/Symbols not displaying correctly in this web forum ???
Can anyone tell me why certain /characters symbols are messed up on this forums web pages ? For whatever reason, all double quotes symbols/characters (") are displayed to me as &quot as in the code exaple below. Also, I run across the symbols &gt and &amp very often in these forums. It is not that big a deal, but it sure makes trying to read code examples difficult. These are the only pages on the enitre www (that I vist) where I expereince these issues. What gives ? Do I need to change browser settings or something ? thanks, tim Range(&quot;O" &a...

Import from Excel
I'm importing a spreadsheet from Excel. My problem is that the column of numbers always imports as text. In using the import wizard it does not allow me to check the data type. It is greyed out with the setting as "text". How do I import numbers? Note that once imported, I have changed to text to numbers but all the numbers loose their precision. Hi Import the data into a temp table - run this through a query and use val function (or change the format) in the query. Use the query to either update or append. HTH -- Wayne Manchester, England. "JeffH"...

Excel 03 DB query pulls data in wrong order, how to resolve?
New user to Excel DB queries. Have set up DB and query, however query does not import DB in the column-order of the query. This worked fine the past couple months. Dont understand why it wont work this month. Have re-created query and the problem still exists. Cant find any information on how to troubleshoot this issue. Thanks for your help. Data>Import External Data>Data Range Properties Uncheck Preserve column sort/filter/layout Does that help? -- Regards, Ron ...

text separated by commas
Hi, I Hope you can help me with this: If I have a row full of data(False or True text) in every column and I want to put in one cell the number of the colums when is true separated by commas. For example: A B C D ...... TRUE FALSE TRUE TRUE by any formula what I need is the number of the columns separated by commas all in one cell 1,3,4 Thanks something like this macro Sub stringtrue() For Each c In Range("a13:g13") If UCase(c) = "TRUE" Then mstr = mstr & c.Column & "," Next Msg...

Change text name based on file name
On my spreadsheet in cell A1 I have a text label containing the word "Department" What I want is when I save the file as Schedule 1 (Division).xls I want cell A1 change from "Department" to "Division" based upon what is within the parentheses in this example Any tips will be appreciated. Thank you. If I understand you, you want to return the portion of the wb's name that is within parentheses. If so, try this formula in cell A1. After doing a "Save As..." you will have to press F9 to update. Minimal testing: =MID(CELL("filename&quo...

Pull unposted quick journal entries into FRx reports
After saving a quick entry, it would nice if this data would appear on the FRx reports when run for posted and uposted data. The Frx report pulls all other items pending posting in the GL, but not the quick entries. I would love to set up quick entries, but I need to see the entries real time, prior to posting. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion...

Excel
Greetings, I would like to cut the first character in each cell of column B. -Example the cell may contain 6008, I would like to change that to 008. Also, cell may contain G302; this should be just 302. Is there a function or macro that i can use to do this? I have found a useful function: =RIGHT(B2,LEN(B2)-1), however it requires a circular reference to work. I also did this: =RIGHT(6004,LEN(6004)-1) which doesn't require a circular reference, however i need to use this function throughout 10 spreadsheets. Can someone help me with this function or module? Thanks, ul...

Auto-text in Form Fields?
I know I've seen the answer to this before, but I can't find it, and I can't remember what it was... I want to set up a Form for a weekly email newsletter that I send out. I'd like the form to have a static Subject line, including the date. Something like: Your Newsletter is Here - 2/28/2007 I know there's a way to put a field marker or something like that within the Subject line, that will automatically set the appropriate date, but I'll be... hmm.... hornswaggled (that's a good word) if I can figure out what it is! Any suggestions? grep grep wrote: &...

Auto-scrolling string buffer
I need a control on my form that will automatically scroll as data is added to it. I have tried this with a CEdit control, but unfortunately any attempts to set the text sets the scroll position back to zero. I can then set the scroll position to the end, but this creates a terrible flickering effect since this control is updated several times per second. Is there any way around this with a CEdit control? Is there a better class / control for this? ---Michael Springer Use Setsel / Replace Sel on it via SendMessage or whatever interface (CEdit) you are using. Just select the last (l...

Pulling out specific information
Hello all, I have a rather sizable spreadsheet that I am working on, it is keeping track of my open and closed stock positions, as well as other things my group wants me to keep track of. anyway I have stocks listed across the top of the screen from column B-DR and I have dates listed in column A going straight down. Now I open a separate worksheet (within the same file) and I know how to type =max(XXX:YYY) for each stock to get its highest price and =min(XXX:YYY) to get its lowest price. But I want it to give me the highest price for each stock along with the date that price occured. Is thi...

How to change text color of an item in a list control
Hi, I'm working in Visual C++ 6.0 and I'm using a List Control (CListCtrl class, used as a report view) in a dialog. This list displays 4 text items, each consisting of 3 columns. Let's consider the following example: Title of column1 Title c2 Title c3 Item1 info12 info13 Item2 info22 info23 Item3 info32 info33 Item4 info42 info43 I want to change the text color of an item when a certain condition is true. In the example above, say I want to change the color of the whole line of 'item2' and ...