How to extract text data from a list of numbers in Excel?

Question

I have created an excel program to extract data I need from a list. This data is then placed into columns with the rest of the rows stating 0. An example of what I have is below starting with A1, B1, C1:

0 0 White
0 Red 0
0 0 0
Blue 0 0

What I would like to do now is create another line of code to take these text cells and place them in order in another column. So I would like to have another column display this:

Red
Blue
White

I am not sure how to extract text data from the 0's and have this display correctly. Can anyone come up with the code to do this?

THANKS!

Answer 1

use the substitute function built into excel to replace the "0" with a blank ""



so if your A1 cell is "0 0 White"



use: =SUBSTITUTE(A1,"0","")

in another cell.



the end result is: White

--------------



if you want to remove spaces in the line as well, use this:



=TRIM(SUBSTITUTE(A1,"0",""))

Answer 2

You can read data by referencing cell

You can also work with range such as sum etc.



google VBA to get more information

Answer 3

put my formula in D1 then auto fill or copy-paste it



=IF(A6<>0,A6

,IF(B6<>0,B6,

IF(C6<>0,C6,"")))



note : i write my formula in different lines to avoid Yahoo replace with ellipsis "...". so write my formula as one