Search : in
By :

Excel formula to get the cell with the text

Last answer on Nov 13, 2009 4:54:29 pm GMT swe, on Apr 27, 2009 8:32:24 am BST 
 Report this message to moderators

Hello,

I need an excel formula to find the part of the text in the range of cells and display the values.
Eg: cell's A1 :A25 has text in it, And B1 :B25 has values in it.
I need to find "ARRA " from A1:A25 and display the values for this frm B1:B25 in C1.
examples for A1:A25 is
A1 ARRA ABC DEF
A2 Non ARRA ABC DEF
A3 ABC DEF GHI
A4 Non ARRA DEF ABC
A5 ARRA abcdef

Here i need capture the cells with only ARRA in it. BUt not NOn ARRA.
Please help.

Thanks in advance.

Configuration: Windows Vista
Internet Explorer 7.0
Excel 2003

Best answers for « Excel formula to get the cell with the text » in :
Excel Formula Display in Cell (Solved) Show
Excel – Formula for cell calculation Show Excel – Formula for cell calculation Issue Solution Note Issue Simple formula of =J3-SUM(L3:X3) BUT if J3 is empty then I want the formula to run as =C3-SUM(L3:X3) What is the proper formula to get the calculation to utilize J3 if...
Basic Excel Formulas Show Basic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
[Excel]changing cell formula to text Show[Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
[Excel] – Countif Formula Show[Excel] – Countif Formula Issue Solution Note Issue I have one excel file in that Column "B" has the "pending or Closed" Status and Column "C" has "Severity 1, Severity 2 and Severity 3" I want to count in one cell with "Pending...
Spreadsheet - Text Operators ShowConcatenation Operator Spreadsheets generally use an oeprator, called a concatenation operator , that allows two text values to be added together. The concatenation operator, written as &, is used as follows: In the above example, the cell...
Ask your question Reply

1

mubashir aziz, on Apr 28, 2009 7:28:39 am BST
  • +2

Just write this formula in C1 and drag it down ....

=IF(A1="ARRA",B1,"")

   
Reply to mubashir aziz

2

swe, on May 2, 2009 1:18:44 pm BST
  • +2

Thanks For the reply.

I need to find the values for that cell ifits "ARRA" or "Non ARRA"
eg:A1:A25
A1 =ARRA 123
A2= Non ARRA 123
A3= 123
and so on.

I need to get the values in B1:B25
whenever A1:A25 = "ARRA" or "Non ARRA"

I used the following formula,It seems to be working but only to that cell if i try to put the same formula in another sheet or another cell ,I don't see the values.

My Formula is as follows.
=IF((OR(A1:A25="ARRA 123",A1:A25="NoN ARRA 123")),B1:B25,0)

Please help.

   
Reply to swe

3

mubashir aziz, on May 6, 2009 3:41:06 am BST
  • +1

It will write value of B in column C if values in A is arra or non arra ......

=IF(OR(A1="ARRA",A1="NON ARRA"),B1,"")

But now you are saying that you want values in B so it means you want to see column A values in B then write below formula in B

=IF(OR(A1="ARRA",A1="NON ARRA"),A1,"")





Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz

4

qadri, on Jul 25, 2009 5:22:29 am BST

Dear sir,
if five name is added in B2,B3,B4,B5,B6,and added number of these peson in C2,c3,c4,c5.,then i want to max number of person name is show in the d2 with the formula.

   
Reply to qadri

5

mubashir aziz, on Jul 27, 2009 4:06:52 am BST

Try this ... hope it will help you ..

=INDEX(B2:B6,MATCH(MAX(C2:C6),C2:C6,0))
Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

6

Netscur, on Aug 24, 2009 6:10:53 pm BST

Mubashir Aziz,
You seem to be a person that has expert level posts on this forum. Similar to you, I would like to think my knowledge of writing formulas is strong. Unfortunately, when presented a problem recently, I am just missing something that might be so basic.

I am building an Excel template (2003/2007). The user will interface with the template and will enter a six digit number into a cell. Utilizing an index/match statement, a formula (below) will seek out additional information from a worksheet containing a raw data extract (which is updated daily) per that six digit number. When a result is found, the data which is pulled then becomes an identifier for another IF/AND formula.

I am utilizing an index match formula (very much like the one you mention in a recent post here in this forum). I need to add an error condition, that if NO match is found, rather than the result being "#N/A" it is something else (i.e. " ", or "Not found").

As you may know, the "/" portion of the “#N/A” result kills the ability to perform additional comparisons with formula's.

This might not even make sense, but to supplement below is the example:

Original formula:
=INDEX('RAW DATA'!H:H,(MATCH(SheetA!B16,'RAW DATA'!A:A,0)))


As previously mentioned, when there is no match in the RAW Data, I need the cell result to be 'an empty cell' or a 'blank space' or something which I can use to match on in additional formulas.

I have tried variations of VLOOKUP’s and found no solution. SUMIF’s can work either because sometimes the resulting data will be text. If you have any advice, I would appreciate it. Over the years I have incorporated IFERROR statements into formulas with perfect results but this one has me stumped.
Thank you kindly for your time.

- Michael a.k.a NETSCUR

   
Reply to Netscur

7

Netscur, on Aug 24, 2009 7:10:38 pm BST
  • +1

Update, I found a solution by using 2 cells to perform the task(s).

The 1st cell contains the INDEX/MATCH formula.
=INDEX('RAW DATA'!H:H,(MATCH(SheetA!D10,'RAW DATA'!A:A,0)))

The 2nd cell contains then evaluates those results with a standard ISERROR condition.
=IF(ISERROR(B10),"none found",B10)
If no error is found, then the results are duplicated (linked), if an error is found, then the result I require ("none found") is populated.


I still am unsure of WHY I couldn't combine the formula into one cell, but at least I managed to find a solution, even if it is inefficient.

Let me know your thoughts.

-Netscur

   
Reply to Netscur

8

mubashir aziz, on Aug 25, 2009 3:34:51 am BST

Thanks for yoru kind words for me. Try this formula and do let me know

=IF(ISERROR(INDEX('RAW DATA'!H:H,(MATCH(SheetA!D10,'RAW DATA'!A:A,0)))) ,"Not Found",ISERROR(INDEX('RAW DATA'!H:H,(MATCH(SheetA!D10,'RAW DATA'!A:A,0)))))





Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

9

Netscur, on Aug 25, 2009 2:53:19 pm BST
  • +1

When I used the formula you provided with the double ISERROR (one at each side of the formula), it fails the Logic test; the results come up "FALSE". So I removed the second ISERROR (see below) and it seems to work perfectly!!!

WORKING FORMULA:
=IF(ISERROR(INDEX('RAW DATA'!H:H,(MATCH(SheetA!D22,'RAW DATA'!A:A,0))))," ",(INDEX('RAW DATA'!H:H,(MATCH(SheetA!D22,'RAW DATA'!A:A,0)))))

Thanks for your help with this one... now I just have to solve two other issues and this template can be handed over to "operations" :)


FYI:
Upon comparing today's successful formula (above) to where I was stuck yesterday, I found that I missed a set of parenthises- which was giving me the error message "too many arguments".

   
Reply to Netscur

10

mubashir aziz, on Sep 4, 2009 5:59:31 am BST
  • +1

So your prolbem soved !
Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

11

Netscur, on Sep 9, 2009 4:03:11 pm BST

Your work was fantastic, I turned the workbook over to Operations last Thursday; they were very pleased with everything.


Now I am trying to learn how to write basic macros for Excel 2007 through our Tutorial package. This website is fantastic, I greatly appreciate being able to lean on you for insight and assistance.

Thank you kindly Muhammad Mubashir Aziz!!!

-Michael C. Minneapolis, Minnesota (USA)

   
Reply to Netscur

12

mubashir aziz, on Sep 10, 2009 2:08:25 am BST

Again thanks and remember sharing knowledge always increase your knowledge so always try to solve others problem as well as yours

cheers,


Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

13

bhavesh, on Oct 27, 2009 8:24:19 am GMT

Dear Sir,

I need an excel formula to find the part of the text in the range of cells and digits in another range display the values.
Eg: cell's A1 :A25 has text in it like "ldd" & "empty", And B1 :B25 has values in it like "20' " & "40' ".
I need to find how much "ldd " from A1:A25 and "20' " in B1:B25 on C1.
examples
a b c d
1 ldd 20' i want value here 4
2 empty 20'
3 ldd 20'
4 ldd 20'
5 ldd 20'

























2

   
Reply to bhavesh

16

Trowa, on Oct 30, 2009 1:51:05 pm GMT

@ bhavesh

Use this formula in C1:

=SUM(IF(A1:A5="ldd",1,0)*IF(B1:B5="20'",1,0))

Confirm this formula by pressing Control+Shift+Enter.

Best regards,
Trowa

   
Reply to Trowa

14

mubashir aziz, on Oct 28, 2009 8:29:44 am GMT

To Count "ldd " from A1:A25 write this formula in cell C1 and it will give you 4

=SUMPRODUCT(--(ISNUMBER(SEARCH("*Idd*",$A$1:$A$25,1))))


and to count "20' " in B1:B25 write this formula in C2

=COUNTIF(B1:B25,"20")

Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

15

Josh, on Oct 30, 2009 1:18:51 pm GMT

Dear Sir

I need an excel formula that will look up a specific job number that is text and numbers in one column and then for all the jobs that match that job number add all the hours together and give me a total of hours for that specfic job number. For example I have a list of job numbers in "column A" that are M17-10, M17-11, M27-6, M27-8, Long Driver-2, Long Driver-3 and in "column F" I have the hours that were charged to that job number. I need the formula to look up say all the M17-11 jobs and then total up all the hours that have been inputed in for that job. Can you please help?


Thanks
Josh

   
Reply to Josh

17

 rerana, on Nov 13, 2009 4:54:29 pm GMT

Hello, You guys seem to have all the good formulas!!

I am looking for a formula that will return the latest text entered in a row.

For exapmle, each week I enter a new information in the form of text at the end of the row to document what happened that week. Each week the databas grows by 1 entry.

What I want is for the executive summary to go to that row and return the exact value (which is text) of the latest entry rather than me having to copy paste the value in the executive symmary.

Any help to figure out how to automate this would be very helpful!!

thank you.

   
Reply to rerana
Ask your question Reply
They need your help