# Solved: Show values of non-blank cell

Discussion in 'Business Applications' started by news, Dec 25, 2012.

Not open for further replies.
1. ### newsThread Starter

Joined:
Oct 20, 2012
Messages:
20
Is there a formula where it will show the text within a range of data?
For example "a1" will show 1-10, "a2" will show 21-30.
columns
a b c d e f g
Row1 0 1-10 0 0 0 0
Row2 0 0 21-30 0 0 0

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,421
First Name:
Wayne
is the text only in one cell in the row and what about numbers

row 1 = 9 , 12, abc , 1, 0,0,0,0, 1-10

do you want to show just abc and 1-10

3. ### newsThread Starter

Joined:
Oct 20, 2012
Messages:
20
Only "1-10" is required to show. a b c d e f g is column in the illustrations

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,421
First Name:
Wayne
but would the other characters exist at all

you could use conditional format to highlight the cell with text
use
istext()
in a conditional format

File size:
9.9 KB
Views:
58
5. ### newsThread Starter

Joined:
Oct 20, 2012
Messages:
20
Refer to your attachment as illustration of what I need.

For cell G3, it will scan through A3 to E3 to capture & display "abc". What formula will allow G3 to have such function?

For my case, G3 will be displaying 1-10 or 11-20 or 21-30 and so on. Only one cell between A3 to E3 will have such 'value'.

Hope above is clearly illustrated.

6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,421
First Name:
Wayne
does this work
=LOOKUP("istext",A3:E3)

only one TEXT value ?

7. ### newsThread Starter

Joined:
Oct 20, 2012
Messages:
20
Yes, I will try it. Thanks.

8. ### Garf13LD

Joined:
Apr 17, 2012
Messages:
455
=INDEX(A3:E3,1,MIN(IF(A3:E3>0,COLUMN(A3:E3),"a")))

Make sure to enter it with ctrl+shft+entr, cos it's an array formula

9. ### newsThread Starter

Joined:
Oct 20, 2012
Messages:
20
Hi etaf,
Your solution don't work..

Hi Garf13LD,
You solution don't work too...

Can anyone share with me how to attach the excel so that you can view the data & come out with the best solution?

10. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,421
First Name:
Wayne
To upload to the forum, open the full reply window ("Go Advanced" button) and use the Manage Attachments button to upload it here.
Full details are available here http://library.techguy.org/wiki/TSG_Posting_a_Screenshot

make sure its dummy data - as you are on a public forum and google will index

11. ### newsThread Starter

Joined:
Oct 20, 2012
Messages:
20
Hi,

Please refer to the attachment, column B.

I have input a formula from B2 to B8, it works. However, the formula doesn't work from B9 downwards.
Any solution?

Because I'm not so good in formula, I input data from K to T.

The concept is if the number in Column A2 (downwards) is 1, B2 will show "1-10"; if 12, B2 will show "11-20" and so on.

File size:
10.3 KB
Views:
52
12. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,421
First Name:
Wayne
sorry, I still do not fully understand what you are trying to do

BUT in the spreadsheet - K to T is all text as you have entered "0" so thats text and not a number - which is why the formulas do not work

if you change the formula from "0" to just 0
then the lookup works - see attached file

Not sure what you really want

File size:
10.3 KB
Views:
37
13. ### Garf13LD

Joined:
Apr 17, 2012
Messages:
455
B2=INDEX(K2:T2,1,MIN(IF(LEN(K2:T2)>1,COLUMN(K2:T2)-COLUMN(J2),"a")))

14. ### newsThread Starter

Joined:
Oct 20, 2012
Messages:
20
Hi etaf,
It really works..... thanks.....

Hi Garf13LD,
I tried your formula and it return "#value!"....

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!