Excel Formula To Skip Cells with text

July 16, 2011 at 21:40:38
Specs: Windows 7
Is there a formula I can use to automatically skip rows with "-" and return only the values of a particular cell(s) that have text in them?


Description of Line Stop
> BODY SHORT (VIOS): U/B SPOTGUN BREAKDOWN
-
> PROCESS DELAY: EXCESS BLACK SEALER ON HEMMING
-
-
-
> OFFLINE UNIT (VIOS)
-
-
-
> OFFLINE UNIT (INVA)
-
-
-
> OFFLINE UNIT (INVA)
-
> OFFLINE UNIT (VIOS): WRONG DASH PNL
> OFFLINE UNIT (INVA): DEFORM W/HOUSE LH
-
-


See More: Excel Formula To Skip Cells with text

Report •


#1
July 18, 2011 at 12:04:07
Sorry, I don't understand what it is your trying to do.

Do you want to return the TEXT string that is in the cell?

MIKE

http://www.skeptic.com/


Report •

#2
July 18, 2011 at 13:30:21
You could filter them...

1 - Data...Filter...AutoFilter
2 - Using the DropDown, select Custom...does not equal...enter the -
3 - Copy the filtered results to a new location.

or

1 - Add 3 blank rows above your data, e.g. A1:A3
2 - In A1 copy your heading - Description of Line Stop
3 - In A2 enter ="<>-"
4 - Data...Filter...AdvancedFilter
4 - Copy to another location
6 - List range: $A$4:$A$32
7 - Criteria Range: $A$1:$A$2
8 - Copy to: $B$1
9 - OK

Your filtered results will start in B1.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
July 18, 2011 at 21:48:45
It's not as elegant as an Advanced Filter, but you could sort them with a "key column" and then delete the dashes.

Add a column of numbers in Column A.

     A             B
1    1   Description of Line Stop
2    2   > BODY SHORT (VIOS): U/B SPOTGUN BREAKDOWN
3    3   -
4    4   > PROCESS DELAY: EXCESS BLACK SEALER ON HEMMING
5    5   -
6    6   -
7    7   -
8    8   > OFFLINE UNIT (VIOS)
9    9   -
10   10  -
etc

Sort on Column B
Delete the rows with the Dashes, which should be all grouped together.
Sort on Column A to put your data back in the original order
Delete Column A

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question