Free Microsoft Excel 2013 Quick Reference

Retrieve a Word in a String that is Uppercase only.

Hi,

To make a long story short...

I need to write a piece of code to analyse a sting (lots of words in the string). The code need to pass though each sentence and extract only the words that are in uppercase only.

The background is that the technical terms that I am looking for are all in Uppercase - whilst the rest of the words are all normal lower case (or proper, with a capital).

I have tried quite a few methods - buy to no avail.

I am familiar with loops and all of that, just need suggestions on the method to use.

And help would be most appreciated.

Puffet101
UK


How do i set up a formula in excel that is the tenth root of 7 versus the
square root of 7?

Using VBA
For each cell in a row, I want to identify a word in a string then
change the format of that word. For example,
For text in a cell " Id: 00098765 Description: For help with this
account number contact XYX Updates: This number is not useful."

I want the color of Id, Description, Updates changed to red and bold
and moved to a separate line in the same cell.
Id: 00098765
Description: For help with this account number contact XYX
Updates: This number is not useful."

Any help will be appreciated

Thank You

Identify a word in random strings in a column and mark adjacent Column true or false.

I have a long list of trade names in a column.(36000)
CHINA CHEF
CHINA CHINA
CHINA CITY II RESTAURANT

I would like to be able to identify all those which have a certain word in this case Restaurant. (not case sensitive). And then show them up in an adjacent column. Like below.
CHINA CHEF FALSE
CHINA CHINA FALSE
CHINA CITY II RESTAURANT TRUE

Excel can do this because I can identify all the cells using find and replace. My objective is to be able ot remove all those that have certain words as they are not what I am looking for. Any assistance would be great. Sadly though I have gone through on line linda’com courses I have a long way to go. I do not mind how I achieve the above.

Here’s hoping to hear from you
All the best
Mark

Is there a function in VBA that is similar to either the FIND function or SEARCH function in Excel? The arguments for the FIND function in Excel are FIND(find_text, within_text, [position]).

I have a text string in VBA ("$A1:$D$13") that I want to be able to identify the first "$" and then later the ":". I'm getting tripped up on the 3rd line of code. Thanks a million.

Code:
Sub page_set_print_area()
    ActiveSheet.PageSetup.PrintArea = "$A$1:$D$13"
    x = ActiveSheet.PageSetup.PrintArea
    Position$ = Search("$", x, 0)
End Sub
Jared

Hi, i hope someone has the answer to my question.
what im trying to do is:
make a formula that will display a word in its cell & another word in a
different cell.
meaning: if i am typing in cell C1 and i use the if statement to compare two
values: IF(A1>B1) if this is true then display the word "OFF" in its own
cell: this part is easy
i would also need it to display the word "ON" in cell D1
if the statement is false, i dont need anything displayed in any cells.
my formula is: IF((A1>B1),("OFF"),("") --> this will display the word "OFF"
in cell C1 if the statement is true and nothing in its cell if the statement
is false. but i dont know how to display the word "ON" in cell D1 at the same
its displaying the word "OFF" in cell C1.

Thank you in advance.

I have been successful in writing code that allows me to print a worksheet
within my workbook and then open up a Word document and print it as well. I
basicly cut and pasted VBA from suggestions by those of you that use this
discussion group. The Word document is located on my harddrive in the same
subdirectory as my Excel workbook. This happens to be my C: drive. I use
this same workbook file at work where the drive is F:. Is there a way of
referencing the document file without the directory drive so that it just
assumes that it resides in the same subdirectory as the Excel file that is
executing the macro? What I have been doing, which I know is rediculous, is
changing the VBA reference drive based on the computer that I am using. Any
help with this would be greatly appreciated. Thanks, Steve

I want to change the font color of a string that is a variable.
This is my attempt, which generates an "Invalid qualifier" error.
The "sActual" is invalid. (Probably because its a string).

Dim sActual As String
'other code here
'
sActual = Replace(Sh4Cell.Value, "-", "", 1, 1)  'a numeric value
'

                            If Sh4Cell.Offset(0, 5).Value <> "" Then
                                sActual.Font.Color = -16776961
                            Else
                                sActual.Font.Color = xlAutomatic
                            End If
I was able to change the font color via, Sh4Cell.Font.Color = "value", but that did not allow the attribute to stay with the value stored in sActual.

How can I set the font attribute and maintain that attribute as I paste the value (sActual) in other areas of the workbook?

Macro sample:
        For Each Sh4Cell In Sh4Range
            If IsNumeric(Sh4Cell) Then
                If Left(Sh4Cell.Offset(0, -4).Value, 1) = "#" Then
                    If Right(Sh4Cell.Offset(0, -2).Value, 1) <> "Y" Then
                        If Right(Sh4Cell.Offset(0, -2).Value, 1) <> "X" Then
                            'sBubbleNumber = Split(Replace(Sh4Cell.Offset(0, -4), "#", "", 1),
"")(0)
                            sBubbleNumber = NumberExtract(Sh4Cell.Offset(0, -4))
                            'MsgBox sBubbleNumber 'for testing
                            sActual = Replace(Sh4Cell.Value, "-", "", 1, 1)
                            'MsgBox sActual 'for testing
                            'Set text color for "Exceed" values
                            If Sh4Cell.Offset(0, 5).Value <> "" Then
                                sActual.Font.Color = -16776961
                            Else
                                sActual.Font.Color = xlAutomatic
                            End If

                            For Each Sh3Cell In Sh3Range
                                'MsgBox Sh4Cell.Value & " " & sBubbleNumber
                                If Sh3Cell = sBubbleNumber Then
                                    'MsgBox Sh3Cell.Value & " x " & sBubbleNumber & " x "
& sActual
                                    If Sh3Cell.Offset(0, 14).Value = "" Then    '0, 14
                                        Sh3Cell.Offset(0, 14).Value = sActual    '0, 14
                                        'MsgBox ActiveCell.Address & "" & ActiveCell.Value & "
" & sActual 'for testing
                                    Else
                                        'Place lowest value in left cell and highest value in right cell
                                        If Sh3Cell.Offset(0, 14).Value > sActual Then
                                            If Sh3Cell.Offset(0, 14).Value > Sh3Cell.Offset(0, 15).Value Then
                                                Sh3Cell.Offset(0, 15).Value = Sh3Cell.Offset(0, 14).Value
                                                Sh3Cell.Offset(0, 14).Value = sActual
                                            End If
                                        End If
                                        'Over write left cell if new value is less than existing value
                                        If sActual < Sh3Cell.Offset(0, 14).Value Then
                                            Sh3Cell.Offset(0, 14).Value = sActual
                                        Else
                                            'Over write right cell if new value is greater than existing value
                                            If sActual > Sh3Cell.Offset(0, 15).Value Then
                                                Sh3Cell.Offset(0, 15).Value = sActual
                                            End If
                                        End If
                                    End If
                                End If
                            Next Sh3Cell
                        End If
                    Else
                    End If
                End If
            End If
        Next Sh4Cell
Any hints, tips or examples are appreciated.

Hi.

I'm searching for words in strings of text, if true one thing, if false something else.

say this is my list:

product1
Total product2
product2
product2 total
product3
product3 TOTAL

(exciting list, huh)

I'd like a yes or no answer as to whether the string contains word 'total' for example, whatever case that may be.

Suggestions>?

Hi, i hope someone has the answer to my question.
what im trying to do is:
make a formula that will display a word in its cell & another word in a
different cell.
meaning: if i am typing in cell C1 and i use the if statement to compare two
values: IF(A1>B1) if this is true then display the word "OFF" in its own
cell: this part is easy
i would also need it to display the word "ON" in cell D1
if the statement is false, i dont need anything displayed in any cells.
my formula is: IF((A1>B1),("OFF"),("") --> this will display the word "OFF"
in cell C1 if the statement is true and nothing in its cell if the statement
is false. but i dont know how to display the word "ON" in cell D1 at the same
its displaying the word "OFF" in cell C1.

Thank you in advance.

Hello,
I am looking for code where I can find and replace a word in a given string. e.g.

I have data in range A1 to A50 which have values as below:

person_name name,
place_name name ,
animal_name name,
item_name name
.
.
. and so on.

Now the first string in A1 is as below

person_name name,

I want to replace second occurrence of "name" (which is after space) in this string with "size" and output should be as below:

person_name size,
place_name size
.
.
and so on

But what I am getting is

person_size size,
place_size size,
.
. and so on.

How can I stop the first occurrence of "name" which is with underscore from not getting replaced.

Can I use some delimiter property or make use of the underscore with the first occurrence of name??

Thanks in advance !!!
Abhi

Can you write a formula in excel that can reference the source/or dependent cells of a specific cell?

In other words: I label my tabs 1,2,3,etc. If I link cell A1 in tab 1 to cell A1 in tab two I would like to be able to write a formula in cell B1 of tab 1 that tells me that cell A1 is linked to tab 1. For a bonus I would like to be able to write a formula in C1 that tells me any cells that are using cell A1. For example if tab 3, A1 was linked to tab 1, A1.

To put it in to formula terms:

Cell A1 of tab "1" would look like this: ='2'!A2
Then I want to write a formula in Tab "1" Cell B1 that results in: "Tab 2" or even "Tab 2 Cell A2"

Can anyone help with this?

I have a formula that will put the name of a tab in a specific cell. This is great for tables of contents and creating titles to documents or footnotes, but it is a pain staking process to use this formula for the above purpose.

That formula looks like this:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,34)

I am currently using 2007.

Thanks to whoever is smart enought to figure this out!

I wanto to hyperlink only a word in a paragraph. This paragraph is inside a
cell. I do not want to hyperlink the whole cell because I need to hyperlink
only a word. Example,
THE FENCE POST CREW PLACED 8 CY CLASS B, MIX C5321(JOBE). A REVIEW OF THE
TICKET FOR THIS LOAD INDICATED THAT THE COURSE AGGREGATE ACTUAL WEIGHT
EXCEEDED ALLOWABLE BATCH TOLERANCE SEE #0018- A1 FENCE.

I would like to hyperlink only the word "see"

Thank you

Hi,

In my excel Macro, I update several values and then open a word linked
with that excel. Finally what I need to do is to print that word to
PDF. I already have a PDF Printer, so I just need the code to do it.
Can anybody help me?
Thanks in advance.
Gast=F3n.

Hi

I want to set up a Web Query that is set in cell A1
the URL will have somewhere in the url the word top

I then want the sheet to automatically bring in the same URL into another cell say G1 but in the url will search the url for the word "top" and change that to the word "left" and bring in that URL data

Then in Cell I1 the exact same same URL from Cell A1 but this time swapping the word top for right

so i am only setting up one URL web query in A1 and then G1 an I1 are auto generating a new url from that original URL and swapping a word in them

is that possible ?

many thanks in advance

Hi,
I was not sure where to post this because I do not know what kind of macro is the best to handle this

We would like to show a chart in PowerPoint that is dynamic. I am not sure if this is an excel macro, a PowerPoint macro, or some kind of Excel embedded in PowerPoint running in the background.

What we want to do is take a chart of several items that appear or disappear from the chart when a checkbox form or activex control is checked. We want this to work in presentation mode of PowerPoint. We want to be able to show the audience all the items and be able to select any order or combination of items and have the chart change based on those selections.

A picture is worth a thousand words so I have included an excel file that generically shows what I am trying to do.

I guess my first question is can I make this excel functionality from my file appear in a PowerPoint presentation and be able to check the boxes while presenting? Anyone know if this is possible or how to do this?

Is there a function in excel that provides the number of distinct periods (i.e. days, weeks, months or years) between two dates?

My work uses a program that only allows our product descriptions to be 50 characters. The descriptions we get are from the original manufacturers. My job (among other things) is to trim the OEM descriptions to less than 50 characters.

The catch to this is that I need to keep the "# P/CS PRICE" - where # could be anything from 2 to 9999 - that I append to the description.

Also when the description is trimmed to 50 characters I don't want it to cut a word in half. So basically it has to include all of a word or none of it.

I have a formula that allows me to trim the description down to what I need except it cuts words. If the cell doesn't end in the appended "# P/CS PRICE" the formula will just trim the description from the left. This is what I have (A1 is the description):

=IF(RIGHT(A1,10)="P/CS PRICE",SUBSTITUTE((LEFT(LEFT(A1,(LEN(A1)-16+FIND(" ",RIGHT(A1,16),1))-1),50-17+(FIND(" ",RIGHT(A1,16),1))))&(RIGHT(A1,17-(FIND(" ",RIGHT(A1,16),1))))," "," "),LEFT(A1,50))

A1= 1.5"FLUSH MNT RET/WATER BAR W ORIFICE 250 P/CS PRICE
After formula=1.5"FLUSH MNT RET/WATER BAR W ORIFI 250 P/CS PRICE

It cuts the word "ORIFICE" to "ORIFI". I'd like it to recognize that it can't include the whole word in the description so it should just omit it. Like so:

1.5"FLUSH MNT RET/WATER BAR W 250 P/CS PRICE

I have to edit 1000's of these descriptions in a day so any ideas or help would be very appreciated!

Dear experts,
I am working on my file matching work for many days. It is too complicated for me :-(
Your help is highly appreciated. thanks a lot.

I would like to write a formula to search a number in Column D which is prefer match to column B and then copy this path in Column C next to matched number. ie. copy D2 copy to C5 which is matched 120175161876

A B C D
1 270005893755 M:medalmike92 - 110176314630medalmike92 - 110176314630.dll.htm
2 270005930155 M:collectpre1945collector - 120175161876pre1945 collector - 120175161876.htm
3 280005685545 M:myhomesabresales1944 - 320157312031.dll.htm
4 280005691948 M:turveytop - 140164093447turveytop - 140164093447 .dll.htm
5 120175161876 M:ukmilitaria - 130157817219.dll.htm
6 280005695745 M:watching7580martinh - 1101812556917580martinh - 110181255691.htm

I have wrote a formula which can do matching with prefect match(not just in string). However there has 100K rows in my file which cannot copy and paste the numbers out from path... :-(

=INDEX($A$2:$A$6000, SMALL(IF(A1=$C$2:$C$10, ROW($C$2:$C$10)-MIN(ROW($C$2:$C$10))+1, ""), COLUMN(A1)))

Can anybody help me to settle this problem?
Zillion thanks

I would like to create a macro in excel that loads a specific word document
in MSword! Any Ideas?

hello friends
can any body tell me how to get the position of a word in a string in excel.
for eg:
pen in pen
i want to get the second pen's position
or

second p's positon

thanks in advance
regards
Kishore

Was wonder if there is a formula that would allow me to remove a word in multiple cells.

For example.
Cells A2 through A300
A1 Spice nutmeg
A2 Spice chili red hot
A3 Spice garlic dry ground

Can I put a formula in a seperate column that will allow me to remove the word "spice".

Thank You,

Hello Again,

I column B I have several hundred names (Multiple words each), many are the same (or at least partially the same). In column C I have dollars and cents amounts.

I would like to type a word in cell F25 and have it search column b for any cell with that word in it and the add the values from column C that corrispond with that is in column B.

I got it to work with this formula (As an array), but i have to match the cell in column B excatly:


I then tried to search for a single word in column B (as stated above) with this formula (again, as an array):

But all i get is a "#Value" error.

Any suggestions?

thanks in advance, you guys are great.

Mark

Hello Everyone,

I am hoping you could help me... here's the situation... in Cell A1 I have a month(January, February etc.) dropdown... I cell B1 same month dropdown... What I'm hoping is a macro that will prevent the user from selecting a month in a1 that is greater than what is in cell b1... so if in cell b1 is April, user can only select the january, february, March and april.

Thank you
Hudas

Hi there,
I want excel to identify a word in a given row, and search the excel tab that has been named with the same word and copy paste the entire row in the tab on the next available row. For eg..

Row 2 in excel consists the words : DOC-MSFT-AAPL-2005.

I would like excel to find the word "MSFT" and find the tab thats named "MSFT" and copy past the entire row ie,..DOC-MSFT-AAPL-2005 in the next availabe cell on the excel tab "MSFT".

Any possibility of getting this done?

Thanks
Joe