Isolate on date by Align Left??

[Gary F Shelton]

I have a standard report that I receive every day from IT and it can't be
altered by IT. So once I have it opened in Excel I can manipulate it but on
column has data in Column B where some of it is Aligned Left and some is
Aligned Right. What I want to do is write a statement in Column A that only
returns data from Column B that is Aligned left. Does anyone have an idea how
to do this?

 

[T. Valko]

By default TEXT is aligned left and NUMBERS are aligned right.

|text | = TEXT
| 12 | = NUMBER
| 12/26/2007| = NUMBER (dates are NUMBERS)

Your subject line says: Isolate on date by align left

What does that mean? What does "date" have to do with it? What type of data
is in column B?

 

[carlo]

Although i have the same problem as biff you could try this in column
A
Cell A1:
=if(istext(B1),B1,"")

maybe that's what you're looking for, otherwise you could make a UDF.
Try to explain better what the problem is, or what your goal is.

hth

Carlo

 

[Gary F Shelton]

The statement =if(isnumber or =if(istext .... work... Now here is my new
problem...

A
1 Apple
2
3
4 Pear
5
6
7
8
9 Peach
10

Is there a way to write a statement so that cells A2 & A3 will say Apple,
Cell A5:A8 to state Pear, and Cell A10 to state Peach? The problem I have is
each change in data is a different item and I don't want to cut and paste
6,000 + rows...

 

[carlo]

You could do following:
in A1:
=if(istext(B1),B1,"")
in A2 and following:
=if(istext(B2),B2,A1)

hth

Carlo

 

[T. Valko]

Assuming that your first entry is *always* a text entry:

text
1
2
3
text
1
text
text
1
2
3

Try this formula in column A and copy down as needed:

=LOOKUP("zzzzz",B$1:B1)


--
Biff
Microsoft Excel MVP


You could do following:
in A1:
=if(istext(B1),B1,"")
in A2 and following:
=if(istext(B2),B2,A1)

hth

Carlo

 

[Max]

.. Now here is my new problem...

Think you could try Debra Dalgleish's page for ways to fill in the col
blanks:
http://www.contextures.com/xlDataEntry02.html
Excel -- Data Entry -- Fill Blank Cells

Fill Blank Cells
Fill Blank Cells Programmatically
(Sub FillColBlanks() by Dave Peterson)


---

 

[Gary F Shelton]

Hi Max or anyone,

I still need some help. I have pasted the code from Dave Peterson into my
active excel fiel and don't know what to do. I have saved the file etc... I
so want to learn how to do this as it will help me daily. So let me go back
to what I have and then maybe someone can help me solve it.

With the Dave Peterson UDF in VBA it should take the data from Column B and
make it look like the data in Column E from a systematic approach.

A B C D
E
1 100102
100102
2
100102
3
100102
4
100102
5
100102
6 100160
100160
7
100160
8
100160
9
100160
10 160100
160100
11
160100
12
13
14 160520
15

Any help in getting me to the next step is greatly appreciated.

 

[Max]

Dave's a subroutine, not a UDF.

You could install the sub in a regular module & run it like this:

In Excel, in any sheet,
Press Alt+F11 to go to VBE
Click Insert > Module

Toggle over to the webpage & copy Dave's Sub FillColBlanks()
[copy all lines until & inclusive of the last line: End Sub]
then paste it into the code window (the whitespace on the right)

Press Alt+Q to get back to Excel

In Excel,
Select the col that you want to fill down from above
Press Alt+F8 to bring up the Macro dialog
Double-click on "FillColBlanks" to run it
(or select "FillColBlanks", click Run)