YouTubersLoveExcel#33: Extract Red Cells

Dear All,

I am facing the following difficulty in excel.

I have a table (matrix) as follows (for illustration purposes I will only use 4 rows, however the actual rows are more than 800):

BLANK BLANK abc BLANK
def BLANK BLANK BLANK
BLANK ghi BLANK BLANK
BLANK BLANK BLANK jkl

consistinf of blank and text cells as above.

I would like somehow to remove the blank cells, preserving the row number (i.e. not shifting cells up)

or,

I would like a way to extract the text cell from each line.

In both ways the results I am trying to find should be a column like this

abc
def
ghi
jkl


Any ideas?

Thanks

I need to do this in Excel: - to extract non-empty cells

source range
[blank]
[blank]
Peter
[blank]
Mary

target range
Peter
Mary
[blank]
[blank]
[blank]

(1D, length of source can vary, [blank] cells can be present/absent, horiz/vertical)
I'd like to achieve the above using built-in Excel functions, with or without sorting. I've been thinking about this for a long time and cannot get what I want, not even close.
If built-in fn is not possible, I may need VBA.
Your advice is greatly appreciated.
-Frankie.

I have a requirement. I need to populate row 14 and 15 with planned budget and planned work. The data has to be extracted from column H and E respectively based on the date range in column F and G and segregate under different Q1, Q2, Q3 and Q4. Please see the attachment

hello
i have an excell file with abt 100 records.
one of the columns is of names. now i want that ´the first name and the
last name should appear in different cells. what will b the formula to get
the first n last names in two different columns depending on a blank space
which separates them in the existing same coloumn of names
thanking in advance

HI Guys

here is the thing i hae 2 list have ALMOST have the same records i want a formula that extract the non duplicate cells

here's an example:-

LIST 1:

@ # $ % ^ & *

LIST 2:
@ # $ %


i want to end up with this:-

^ & *


i hope you get th point guys cuz my list contains at least 2000 record

please can you tell me which formula i should use and please dont tell me the VLOOKUP & FILTER cuz they dont work

I have a requirement. I need to populate row 14 and 15 with planned budget and planned work. The data has to be extracted from column H and E respectively based on the date range in column F and G and segregate under different Q1, Q2, Q3 and Q4. Please see the attachment.

Basically i want Q1 data to be populated in Q1 column and similarily for Q2, Q3 and Q4

I am trying to write some VB code which can modify a cell's data. Column A contains the main string. What I want to do is look at each cell in Column A and if the slash character "/" exists then do one of two things:

1. If the string is "/L" then copy the "L and any text after that to the same row in column G
2. If the character after "/" is anything else then don't copy anything to Column G

Next, in the column A data, only keep the text preceeding the "/".
If there is no "/" in the cell, then do nothing.

Basically I have a column of serial numbers but only some of the cells have extra information which is displayed after the "/" character.

eg. If cell A1 has the string "aaa123/L001" , I want cell A1 to now read "aaa123" and cell G1 to read "L001"
eg2. If cell A1 has the string "aaa123/Server", I want cell A1 to now read "aaa123" and cell G1 empty.


Thanks for your help.

Hi Guys, new to this forum....

I have a question around extracting initials from two cells.

I have two cells FName LName.

Example: "JOHN" "SMITH"

I know that a simple formula of =Left(A1) & Left(B1) would give me my initials.

"JS"

What im after is a solution to the problem of the surnames being in the following formats:

1) "Smith Jones"
2) "Smith-Jones"
3) "o'shea"
4) "McDonald"

and returning

1) JSJ
2) JSJ
3) JOS
4) JMD

Could someone possibly point me in the right direction?

Hello all,

I am trying to extract % discounts on payment terms from Column A in my worksheet. Below is an example of how they look.

In the adjacent column (Column B), I need to input a formula that will keep ONLY the % discount from the text on the left (Column A). If there is a blank space (i.e. A4), just put a 0 in column B (B4). Currently the entire Column B is formated as a percentage with 2 decimal spaces.

I can't seem to figure out how to do this. I was thinking about taking the total length of the cell and subtracting where the % was found, but when I tried using a =FIND("%", A1, 0) to get the location of the percentage sign, I kept getting a #NA message.

Any help would be appreciated!

~ Im2bz2p345

Sheet1

A 1 2% 30 Net 31 - ACH 2 2.25% 30 Net 31 - ACH 3 2.15% 30 Net 31 - ACH 4 5 2.00% 6 1.00% 7 0.50%

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I am trying to create a formula that will show the lowest of three separate cells. I don't know how to do the Vb script, so if at all possible, please don't suggest that. This is what I need:

Cell H34 HOURLY RATE (=$920)
Cell I34 DAILY RATE (=$980)
Cell J34 MONTHLY RATE (=$1020)

Cell K34 displays the text "HOURLY" since it's the lowest of those 3 options