Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

find text in cell

Hi all,

I need a macro that will find a cell by its contents (text) and then it will add the value of 1 to the cell under it IF the is data in the "E" cell of the same row. I would also need for it to do the same (add +1) for every other "A" cell under it.

Any help is appreciated

Pedy


Post your answer or comment

comments powered by Disqus
I have used this code to find certain "text markers" which identify, for example, column headers.

	VB:
	
Cells.Find(What:="ST:", After:=ActiveCell, LookIn:=xlValues, LookAt _ 
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
True, SearchFormat:=False).Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Use Case
Record entries are in rows. A row may have one or more comments, each in separate columns. A comment may include a Status flag, which includes the prefix of "ST:". I only want to find the last comment that includes a status string, since that should be the most recent status for that record (in rows). Some records may not include any comments that contain status strings.

So, the desired variation on the above cells.find search is this:
What: Identify a "marker" string to find. In this case, it is "ST:", case sensitive.Where: Search only in the active row, where I have already identified the last filled column in that row, which is the most recent entry.Direction: Search from right (last column) to left (column A) and stop on the first match. The marker, "ST:" will not be the only text in the cell.While I would prefer to use an efficient command like cells.find, I know that I can also move across the row right to left and check each cell. I have seen at least one other forum entry taht Where elegance falls short, brute force will get the work done.

If a target string is found in a cell, I can do the rest to extract the status text (8 characters) that come after the "ST:" marker.

Many thanks for suggestions if there is a single command solution. I am programming the brute force - cell-by-cell - method in the interim.

Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

I think this is really easy to do.... I need a macro that looks in column F for a cell with "TEXT" in it. When it finds it, it selects it and centers the screen on it. Then if I push the button I am going to assign to this macro again, it continues from where it is now and finds the next cell in the same colulmn F that has "TEXT" in it. So for example the first time I hit it, it might find "TEXT" in F456 and center on it, then I hit it again and it finds it in F512 and centers on that, etc, etc. Can someone help me with this?

Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

Dear all, I'm pretty muddled with this & would appreciate your help!

I'm looking for a cell reference solution to finding any "text/number" in a given array & returning an adjacent cell value.
The adjacent cell being called should be dynamic.. could be (3 rows down, 1 right) or even (5 rows down, 6 right).

For eg, cell B2 will contain the formula, to find the text "Syllabus e01932 summer" in my given array & then returning its adjacent cell value, "26" (3 rows down, 1 column right).

Please see the attached screen shot/workbook!
I wish to solve this using cell formulas & not vba macros.

Thank you,
eb

Find text in array return adjacent cell value.JPG
Find text in array return adjacent cell value.xls

Hello,

I'm trying to find a way to find/search for text within an Excel workbook. I would like to have a user enter text into a cell and have the results of their search list under the cell in which they typed in.

If this is not possible, then is there a way to click a link on a cell to the FIND dialog box?

Please help.

Searching and finding text in this excel file would make everyone's life easier in my situation.

Thanks,

Bill

Hi all,

I am trying to change the text in cells already populated, so if they have a "?" in the cell i want it to be changed to "N/A". I have tried a 'find and replace' all but it overwrite some of the value that aren't "?". I decided to write a macro but it is coming back with an error saying 'object require' when it tries to overwrite a cell. The code is detailed below, please help. I am also sure that this code could be tidied up but not sure how

Sub ReplaceQuestionMarks()
Dim i As Integer
Dim k As Integer

Range("B5").Select
For i = 1 To 61
If ActiveCell.Text = "?" Then
ActiveCell.Text = "N/A"
End If
If ActiveCell.Offset(0, 1).Text = "?" Then
ActiveCell.Offset(0, 1).Text = "N/A"
End If
If ActiveCell.Offset(0, 4).Text = "?" Then
ActiveCell.Offset(0, 4).Text = "N/A"
End If
If ActiveCell.Offset(0, 5).Text = "?" Then
ActiveCell.Offset(0, 5).Text = "N/A"
End If
If ActiveCell.Offset(0, 8).Text = "?" Then
ActiveCell.Offset(0, 8).Text = "N/A"
End If
If ActiveCell.Offset(0, 9).Text = "?" Then
ActiveCell.Offset(0, 9).Text = "N/A"
End If
If ActiveCell.Offset(0, 12).Text = "?" Then
ActiveCell.Offset(0, 12).Text = "N/A"
End If
If ActiveCell.Offset(0, 13).Text = "?" Then
ActiveCell.Offset(0, 13).Text = "N/A"
End If
If ActiveCell.Offset(0, 16).Text = "?" Then
ActiveCell.Offset(0, 16).Text = "N/A"
End If

ActiveCell.Offset(1, 0).Select
Next i

Range("B68").Select
For k = 1 To 46
If ActiveCell.Text = "?" Then
ActiveCell.Text = "N/A"
If ActiveCell.Offset(0, 1).Text = "?" Then
ActiveCell.Offset(0, 1).Text = "N/A"
End If
If ActiveCell.Offset(0, 4).Text = "?" Then
ActiveCell.Offset(0, 4).Text = "N/A"
End If
If ActiveCell.Offset(0, 5).Text = "?" Then
ActiveCell.Offset(0, 5).Text = "N/A"
End If
If ActiveCell.Offset(0, 8).Text = "?" Then
ActiveCell.Offset(0, 8).Text = "N/A"
End If
If ActiveCell.Offset(0, 9).Text = "?" Then
ActiveCell.Offset(0, 9).Text = "N/A"
End If
If ActiveCell.Offset(0, 12).Text = "?" Then
ActiveCell.Offset(0, 12).Text = "N/A"
End If
If ActiveCell.Offset(0, 13).Text = "?" Then
ActiveCell.Offset(0, 13).Text = "N/A"
End If
If ActiveCell.Offset(0, 16).Text = "?" Then
ActiveCell.Offset(0, 16).Text = "N/A"
End If
ActiveCell.Offset(1, 0).Select
End If
Next k

End Sub

Hi,
I am having a littler trouble with using a function to find text (last name) in a table and then return full name from the table. See attached sheet with example of what I am looking for.
A quick explanation:
Have a table with columns that have team and name of player. My input is the last name of the player and the team. Need a fomula that searches all the table and then returns the full name of player based on 2 conditions of last name and team.

I have 1700 cells that have to add text to, it already has text in it, and I need that to stay there what I need to add is "catalog/"before the default value and ".jpg" after the default value with out any space inbetween. eg, text in cell currently "picture" in A1 and "picture2" in A2, after its done it should look like this "catalog/picture.jpg" in A1 and "catalog/picture2.jpg" in A2, is there an easier way to do this then to go cell by cell and add catalog/ and .jpg thanks.

Hi,
I want to be able to put some text in a cell. I would then like this text to be displayed in other cells on other sheets, but I want the text displayed to be dependant on what the text says in the first cell.
Therefore if I put Jumping in cell A1 on sheet 1 I would like it to display the same text in cell B6 on sheet 2, but when I change the text in A1 Sheet 1 it automatically changes the text in B6 Sheet 2.

Is this possible in Excel or not ????

Thanks

Dave Steele

Hey everyone question

here is a formula i would like in cell D20 (might not be a formula out there,maybe more code)
if cell C20= "S" then text in Cell D20= "SHOP" (then will be able to type after "SHOP")

if cell C20="F" then text in Cell D20="Field" (then will be able to type after "Field")

I have many facters (15 to be exact) that I would like to do this in the range of D20:D38

Any ideas on how to do this?

I would like to Compare two columns of data in order to identify in what rows the same results (same piece of text in cells) will appear. As shown below the same data (Service_Desk) is a part of the cell. In the case below row 3 and 4 will comply as Service_Desk appears in the same row in the two columns.

snt1-HDK-Service_Desk xxxx-TS2-ISDM_Support
teb1-HDK-Service_Desk snt1-OSS-On-Site_Support
vwc1-NL-HDK-Service_Desk snt1-HDK-Service_Desk
vwc1-NL-HDK-Service_Desk vwc1-NL-HDK-Service_Desk
xxxx-NL-TS2-Wintel_B1 xxxx-NL-TS2-Storage

I don't want to see this #N/D text in cells when they occur

so for example to see example what is it about i have this formula:
=MROUND((Q104);CHOOSE(MATCH((Q104);
{1,01;2;3;4;6;10;20;30;50;100});0,01;0,02;0,05;0,1;0,2;0,5;1;5;10))

so when in Q104 there is no any number i receive this #N/D but i don't want this to see.
Can i solve this without some conditional formatting and solve it in more easier way in formula or something different?

Hello.

I've noticed a limit of text in cells. Is there a way to change this?

The input in a cell is like this:

"Utnyttja VSIB (Scorecard) samt Service level underlag för PipeChain
leverantörer för faktabaserad bedömning, samt att alltid informera
leverantörer att de kan få tillgång till Scorecard och därmed inbjuda
leverantören till en mer aktiv roll i ett eventuellt
förbättringsarbete" Swedish

I have formated the cell to wordwrap but it only shows #########

Can I change it?

--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=479460

Hi there

i have just started working on Vb code to keep track of file changes on different environments like dev, test, uat, stage and production

What i want to know is the following

Is it possible to compare texts in cell?
Is possible to pass to a cell, the file directory of where the contents of the release are?
Is it possible to read folder contents i.e. read dll names, aspx names etc into another cell value?

please advice

Need to find text in an excel sheet. CTRL+F will only work once. When I try
again for text that is cleary there it doesn't respond.

Hello,
in sheet1, I would like to add text in cells in Column C from a range of row (by FirstRow to LastRow.

In Cell C11 to C?? (LastRow) i would like to add "U-".
does anyone could help me to perform this ?
Thanks in advance.
Herve

Hi,

I was wondering if it's possible to align text in cell as "justify"
just like in Word?

An is there any chance of spelling also =)

Yours,
Sirri

I'm trying to use vlookup to find text in a sheet and then pull data from that row. The column that I'm trying to reference contains restaurant items and their item number (i.,e "Calamari 100345). The problem is that I just want to use the item number to pull the data with. I've used vlookup when dealing with numbers but not text. Not sure if I can search for just the numbers within that field. Thanks!

Hi all,

I had a bit of an accident which made me lose a lot of files, inclusig a file with lots of macro's in it.
I had a backup but this CD appeared to have an error so now I am ampty handed.

Most macro's I can reproduce (and have) except 1.

This macro looks for text in different selected area's and is used for different files.
E.g. DNF, Fall, DQ etc

I had a macro where first there was an array defined and later (using Uppercase) this array was used in the procedure that looked for the text.
The UpperCase was needed because then the macro looked for both Upper and lower case letters

I believe the array looked like: array = ("D"; "F"; "<") etc

Who can help me reproduce this macro?

All help is appriciated

Thanks
Hein

Hello,
In an Excel File, Sheet1, I have a text in Cell C11 to C200.
Does Anyone know a macro to Add "tititi" at the biginning of the text in these cells (C11 to C200)
Ex: In Cell C11 I have "QWERTY" and I want "tititiQWERTY"
In Cell C12 I have "AZERTY" and I want "tititiAZERTY"
Regards
H.

Hi all,
i have problem with long text in Excel.The text in cell is trimmed although the cell height is enough to display the text.

http://carnivore.ic.cz/excel.JPG

hello all..
i confused with this problem.
cut or delete some text in cell...

i have 1.000.000 rows , wanna remove some text in cell..
example :

st. causeway bay kav.19 hongkong 12345
st. harbour rd.19 hongkong 12345
st. yamatee north rd.29 hongkong 12345

must change to :

- street. causeway bay hongkong 12345 --> kav.19 removed
- street. harbour rd.19 hongkong 12345 --> rd.19
- street yamatee north hongkong 12345 --> rd.29 removed

only street name and country name also zip code will be posted, number road and other text word must removed.

st to street can use replace function , but how to remove text number, road, and other text word.

any function or logic to do this ???.. please help me friends. this my first job at office.
i attached file here. "
thanks be4

I am trying to organize a worksheet so that when I enter a number or text in cell D3 a figure is automatically entered into cell G5, If a number (part number) is entered a value of the part appears in G5.
probably a simple task but I just cant get my head around formulas..

Thanks in advance

awesome solved by Mr. Marcol

hello all..
i confused with this problem.
cut or delete some text in cell...

i have 1.000.000 rows , wanna remove some text in cell..
example :

st. causeway bay kav.19 hongkong 12345
st. harbour rd.19 hongkong 12345
st. yamatee north rd.29 hongkong 12345

must change to :

- street. causeway bay hongkong 12345 --> kav.19 removed
- street. harbour hongkong 12345 --> rd.19 removed
- street yamatee north hongkong 12345 --> rd.29 removed

only street name and country name also zip code will be posted, number road and other text word must removed.

st to street can use replace function , but how to remove text number, road, and other text word.

any function or logic to do this ???.. please help me friends. this my first job at office.
i attached file here. "
thanks be4


No luck finding an answer? You could always try Google.