If cell in range contains ANY text, return value of that cell

[ajoshi87]

Hello everyone,

I have a range of cells A2:A15 and I would like to write a formula that says, if there is a cell from A2:A15 that has ANY text, return the value of that cell.

As a caveat, that range of will never have 2 cells that have text.

Any help writing this formula would be appreciated

 

[T. Valko]

Hello everyone,

I have a range of cells A2:A15 and I would like to write a formula that says, if there is a cell from A2:A15 that has ANY text, return the value of that cell.

As a caveat, that range of will never have 2 cells that have text.

Any help writing this formula would be appreciated

What version of Excel are you using?

 

[ajoshi87]

Excel 2007.

 

[Jade Coon]

=LEFT(A2,20) this will return up to 20 characters or you can change 20 to any number that you want then just drag the formula down.

 

[ajoshi87]

@Jade, thank you for the suggestion! Is there a way this could work for a range? So for A2:A15 instead of just A2?

 

[T. Valko]

What version of Excel are you using?

Excel 2007.

Try this...

=IFERROR(INDEX(A2:A15,MATCH("*",A2:A15,0)),"")

 

[ajoshi87]

Try this...

=IFERROR(INDEX(A2:A15,MATCH("*",A2:A15,0)),"")

@T. Valko - that unfortunately did not work - it still returns a blank cell even if there is text in cell A14. Is there something I should be writing in for the "*" or is it ok to leave that as is?

 

[proficient]

@T. Valko - that unfortunately did not work - it still returns a blank cell even if there is text in cell A14. Is there something I should be writing in for the "*" or is it ok to leave that as is?

@AJOSHI: Formula is working properly...

Excel Workbook
	A
1	1
2	2
3	3
4	2
5	Kamran
6	2
7	3
8	6
9	5
10	4
11	7
12
13	Kamran
Sheet3

 

[ajoshi87]

@proficient,

I just tried it in a blank sheet and yes the formula is working. Unfortunately, when I input the same formula into my actual worksheet it does not work. Just as a reference, cells A2:A15 are formulas as well instead of raw inputted text - would this make a difference?

 

[T. Valko]

@proficient,

I just tried it in a blank sheet and yes the formula is working. Unfortunately, when I input the same formula into my actual worksheet it does not work. Just as a reference, cells A2:A15 are formulas as well instead of raw inputted text - would this make a difference?

You said you were looking for any TEXT so the formula I suggested is looking specifically for TEXT. If you have formulas that return formula blanks "" these are actually TEXT entries and the formula I suggested will "see" these blanks. So, let's try something different.

Try this array formula**:

=IFERROR(INDEX(A2:A15,MATCH(TRUE,A2:A15<>"",0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula will look for cells that do not contain blanks.