evaluating text cell contents

I have a large number of cells with nothing but text in them. I want to 
evaluate the first few words in each and then create a macro to move the 
contents of that cell to a new column depending on the cell's contents. Any 
creative ideas out there? I am using Excel 2000.

0
B1 (229)
1/3/2006 9:12:03 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
583 Views

Similar Articles

[PageSpeed] 26

Make a list on a new worksheet of those words/phrases.

Put them in A1:Axx.

Then loop through your cells looking for those words/phrases.  If you find
it/them, then move the contents and go to the next cell.

dim ListWks as worksheet
dim wks as worksheet
dim myRng as range
dim myCell as range
dim myListRng as range
dim myListCell as range

set wks = worksheets("sheet1")
set listwks = worksheets("list")

with wks
 set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

with listwks
 set mylistrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with


for each mycell in myrng.cells
  for each mylistcell in mylistrng.cells
    if application.countif(mycell.value,"*" & mylistcell.value & "*") > 0 then
      mycell.offset(0,1).value = mycell.value
      exit for 'already moved it
    end if
  next mylistcell
next mycell

=======
Watch out for typos--I didn't test this.


Dave B wrote:
> 
> I have a large number of cells with nothing but text in them. I want to
> evaluate the first few words in each and then create a macro to move the
> contents of that cell to a new column depending on the cell's contents. Any
> creative ideas out there? I am using Excel 2000.

-- 

Dave Peterson
0
petersod (12004)
1/3/2006 10:51:39 PM
Reply:

Similar Artilces:

If specific cell is not empty, copy (or enter) formulas and format
I have a workbook that has several sheets that refer to each other with formulas throughout. The first sheet is a block of data where I enter data into columns E, G, H, P, R, S, T, U and Y. Columns A, B, C, D, F, I, J, K, L, M, N, O, V, W and X all have formulas in them (many are VLOOKUP and some are calculations). These cells all have a certain format as well (that differs from column to column), such as date format, number format (some are percentages, some are decimals with 8 decimal points, etc), conditional formatting differs for each row, etc. That being said, I n...

Aligning text
XP and 2007. I am copying and pasting text from a PDF into PPT. In PPT, when I have a bullet with two lines of text, the bottom line of text does not align with the top line. It is one space to the left. If I use the middle hanging indent mark on the ruler to move the bottom line to the right one space, the top line moves also and I must then place the cursor in front of the top line and delete a space. This is 3 extra key strokes for almost every bullet. I have tried everything I know to do. Any help will be appreciated. Thanks. There may be a way around it using code, but as ...

How do I make text italic in a publisher text box?
I've copied an item into publisher. The font automatically changed. I changed the font back to what is was originally. That worked, along with items that were once again bold. However, the entire text WAS italicized and now it's not. How do I italicize my text now? Select the text and italicize it. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "81gator" <81gator@discussions.microsoft.com> wrote in message news:35607A56-CAE8-4E6D-8FD3-FC6545B2AA9A@microsoft.com... > I've copied an item into pu...

Save text from CList into file
Hi, guys; I found a small problem with this. I thought it wasn't important but to be honest it's becoming a real pain in the ass. The question is the following: I've got a Clist with several lines created in the Dialog. At the end of the dialog, I want to save that text in a file. The code is this: void CTranslator::OnEditorEnd() { // TODO: Add your command handler code here char szFilter[] = " *.txt"; CFileDialog FileDlg( TRUE, NULL, NULL, OFN_HIDEREADONLY, szFilter); if (FileDlg.DoModal() == IDOK) { try { CFile cf(FileDlg.GetPathName() + ".txt&quo...

problem with Data|Text to Columns
I wrote a while back and was possibly not clear - I do a lot of data import and clean up - on this new computer, the subject topic, which I call data parsing, does not work correctly. I have to divide the columns with one extra space, which I never had to do before. I hate to think of uninstalling excel, but if that's what it takes . . . Any advice would be gratefully appreciated. Happy New Year -- Regards, P D Sterling New York, Texas & Texas, New York P D Sterling" <pdsterling@hotmail.com> wrote in message news:%23sslSLUTIHA.4656@TK2MSFTNGP03.phx.gbl... > call...

Help!! Excel question: how to link a cell to a group of cells
Hi, Good day! I have a worksheet: A commentA1 --------------- B commentB1 --------------- A commentA2 ---------------- C commentC1 --------------- B commentB2 .... etc and want to create another worksheet to link to the first to look like: A commentA1 commentA2 --------------------- B commentB1 commentB2 --------------------- C commentC1 Can this be done in Excel? Thanks, lc Hi Luican............. In Sheet 2, wherever you want the contents from "Sheet 1 Cell A1", put this formula =Sheet1!A1 In Sheet 2, wherever you want the contents from "She...

Date value in cell with text
I would like a cell in Excel 2000 to have some text plus today's date, like this: "Total orders as of 3/09" I was able to pull the current date with =NOW() and right clicking the cell and choosing the date to look like 3/09 I was thinking that my formula should look like ="Total orders as of "&NOW() Any thoughts? ="Total orders as of "& TEXT(TODAY(),"m/dd") -- Kind regards, Niek Otten Microsoft MVP - Excel <tonyrulesyall@yahoo.com> wrote in message news:e37153e1-42c4-4dce-892f-ed1362033ec3@41g2000yqf.googlegroups.com... >I...

Downloadable evaluation version of Outlook 2003
does anyone know if it possible to get a downloadable version? Thanks.....Sly Sly wrote: > does anyone know if it possible to get a downloadable > version? > > Thanks.....Sly No. Not full Outlook, only Outlook Express is downloadable along with IE... >-----Original Message----- >does anyone know if it possible to get a downloadable >version? > >Thanks.....Sly >. > ...

Can I format an entire row based on the value of a single cell?
I would like to use the conditional formatting feature to format an entire row rather than just a single cell. For example: If the cell value = "Total" then the entire row is bold. select your range (multiple rows???). Then with A3 (say) the activecell in that selection: format|Conditional formatting formula is: =$a3="total" apply a nice format LTShelley wrote: > > I would like to use the conditional formatting feature to format an entire > row rather than just a single cell. For example: If the cell value = "Total" > then the entire row is bo...

How do you force another line in a text box?
When using text boxes. how do you force another line when character spacing reaches a certain point. A text box is a single line. Full stop. A text area will automatically wrap when the user types to the end of the visible space - this does not, however, add a line feed. To add a line feed after a preset number of characters, as I said in an earlier post, you need server side scripting which parses the data, and adds the line feed at the required intervals. Of course, this is complicated by needing to account for spaces between words - and avoiding the line feed in the middl...

How do I unwrap text to columns?
I have a spreadsheet where the addresses have been saved as wrapped text in one column. I want to spilt the addresses into columns as Address1, Address2 etc but I can't enter the wrap character (which I think is ALT+ENTER) as a delimiter in the Text to Columns wizard. I have also tried search and replace but again Excel will not recognise ALT+ENTER as a delimiter Any ideas much appreciated! Thanks Re-format the column to eliminate the wrap.............then use Data > TextToColumns with Alt010 (from the keypad) as the delimiter Vaya con Dios, Chuck, CABGx3 "ChristineR&...

Export text in ""
I want export (as a csv file) some data to create a TomTom POI file. I have Latitude, Longitude, Name I need to export the Name column in " " eg: 53.5,-0.8,"Name of location" Rather than: 53.5,-0.8,Name of location Any way to do this? -- Regards, Dean Thought I'd worked this out but it didn't work :-( Applied a custom number format to the column of \"@\" This displays " " round the text but doesn't export them to the .csv file. -- Regards, Dean "Melbridge" wrote: > I want export (as a csv file) some ...

How do I embed images, text boxes, etc. in Publisher?
I'm using Publisher to send off an email newsletter but on some internet emails, they show up with attachments or are jumbled all over the page. Can anyone tell me hom I can overcome these two issues to send my newsletter? Cheers. Ricardo Not all folks will accept HTML email. Send the file as a PDF. There are free converters around. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Ricardo@turrific" <Ricardo@turrific@discussions.microsoft.com> wrote in message news:428E4744-70C7-4EAA-82F3-76E6AAED8EC5@micros...

How can I put cells in front of a picture?
I have a bunch of state .pdf forms that I want to link into sheets so that I don't have to retype the common info over and over. I still want to be able to type into the other cells (the non-linked ones). I took a snapshot of the page and turned it into a GIF, made the appropriate areas transparent and put it on the Excel sheet as a picture. I was going to align the cells to correspond to the state form, but the picture is in front of the cells - so I can't get to them. I could put the pic back after I link the cells but then I couldn't type into the non-linked cells. I...

Calculating percentages of number of occurence of a text value
I have a field that is called txtpurpose. The value of this field in any record could be 4 different values " Re-financing", "Corporate Finance", "Buy- out", or "Restructuring" For a particular month (a field txtmonth) I want to count how many records had each of these values and what percentage that number was of the total number or records for that month. So I want to end up with a reuslt like this: Re-financing 46% Corporate Finance 38% Buy-out 12% Restructuring 4% Total 100% Can someone start me off in the right direction please? Many ...

Evaluating text as formulae
Is it possible to refer to text in a cell as though it were a formula, ie execute it instead of displaying it? Eg if the result of your formula was "A1 + B2", to actually add A1 and B2 and display the result? Thanks Nigel Like concatenating text? =concatenate(a1," + ",b2) or =concatenate(a1,b2) =concatenate(a1," ",b2) "Nigel Ramsden" wrote: > Is it possible to refer to text in a cell as though it were a formula, ie > execute it instead of displaying it? Eg if the result of your formula was > "A1 + B2", to actually add A1 and...

Have you evaluated...
Has anyone evaluated Celerant Command Retail or Tomax Retail.net POS solutions? How are they compared to RMS in terms of features and prices? ...

RE: Font size displaying too large for cells in locked spreadsheet
This spreadsheet resides on a common drive, but one user is unable to to view some cell contents. The best that I can tell is that the font size is too big. Changing screen resolution has no effect, neither does zooming in and out. Other company users do not have this problem. Does anyone have an idea what I might try? ...

How do I merge in a selected range of cells out of Excel?
copy / right click / insert copied cells ...

Text import out of line?
I have the following Macro.... Can you help me to eliminate the use of any cells on the import? It comes in all chopped up but I just want it as the text file is that I am bringing from Filename:="G:\Gas_Control\EXCEL\DEPT\Y2K\Weaternet Forecast\cgasdisc.txt Any HELP would be greatly appreciated. Sub Text() ' ' Text Macro ' Macro recorded 12/21/2004 by Kreft Dim destCell As Range Dim totConspWkbk As Workbook With Workbooks("DegreeDays.xls").Worksheets("text") .UsedRange.Clear Set destCell = .Cells(.Rows.Count, "A...

Blank Cell if next cell value is 0
Hello: Please help me with this one: I like to have these cells: A1: Blue A2: 0 A3: Red A4: 1 A5: Green A6: 0 A7: Yellow A8: 7 convert to these: A1: A2: 0 A3: Red A4: 1 A5: A6: 0 A7: Yellow A8: 7 (if A2 value is 0 (zero), A1 should be blank and so on... Thanks in advance. Dervish Hi do you want a macro solution for this? or would be an output an an adjancent column be sufficient? -- Regards Frank Kabel Frankfurt, Germany "Dervish" <dervishme@comcast.net> schrieb im Newsbeitrag news:400aa860.0410281053.2aa1e388@posting.google.com... > Hello: > > Please help m...

Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...

Pinrt four line texts
#include<iostream> using namespace std class CPrint { public: ~CPrint() { cout<<"b,\n"<<"c,\n"<<"d,\n"; } }; CPrint g_print; int main() { cout<<"a,\n"; return 0; } --------------- cannot change the main function, how can I printf folowing result: a b c d ---------------------------------- If I change head file to iostream.h and delete the "using namespace std", the right result will appear. Why? my email is fisheryj # gmail.com No person know how to di it? <fisheryj@gmail...

cells evaluation question
Hey I have a spreadsheet with 9 cells (c14:c22) They will have integers entered from 0 to 10 I need a formula to do the following: - Find how many have a value >=5 - Get the total value of the cells with a value >=5 Thanks Simon You want Countif and Sumif, as in: =countif(c14:c22,">=5") =sumif(c14:c22,">=5") Regards, Fred "Simon" <Simon@discussions.microsoft.com> wrote in message news:8D2F8EA3-D774-4A65-9157-274051900E28@microsoft.com... > Hey > > I have a spreadsheet with 9 cells (c14:c22) > They...

Turning Cell into Hyperlink
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a column in spreadsheet that has URLs. I would like to turn them into hyperlinks so I can go directly to the webpage from Excel. <br><br>If I type in the URL or if I go cell by cell and use CMD-K I can do it but is seems like there should be a way to select a bunch of cells with URLs and convert them from text to hyperlink. <br><br>Can someone help? Did you search this group for "Hyperlink"? There are several postings describing the process in exact detail. I don...