choose last cell with a value in a column

[jcheko]

need to figure a function that identifies the last cell with a value (could
be a number or a letter) in a column....I will be updating this every other
day, it should be able to identify the next cell if I add a value to the
column. for your help I am very thankfull.

 

[Gary''s Student]

=SUMPRODUCT(MAX((ROW(A1:A65535))*(A1:A65535<>"")))+(A65536<>"")

will tell you where the last value is, number or not.

 

[Ron Rosenfeld]

need to figure a function that identifies the last cell with a value (could
be a number or a letter) in a column....I will be updating this every other
day, it should be able to identify the next cell if I add a value to the
column. for your help I am very thankfull.

Are there blanks?

If there are no blanks, you could use the COUNTA function to determine the
address.

For example, if your first entry is in A3, the address of the last cell with a
value could be given by the formula: =ADDRESS(2+COUNTA(A3:A65534),1)

If there might be blanks, you could use this formula:

=ADDRESS(MATCH(9.9E+307,1/(A:A<>"")*ROW(A:A)),1)

This formula must be **array-entered**:

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

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

If you have a version of Excel prior to 2007, then change the formula so as not
to refer to an entire column.

e.g. instead of A:A, use A1:A65534

--ron

 

[Ms-Exl-Learner]

=INDEX(A1:A65535,MAX(ROW(A1:A65535)*(A1:A65535<>"")))

Copy and paste the above formula and give F2 and press CNTRL+SHIFT+ENTER,
since it is an array formula.

Change the cell reference to your desired cell, if required.

 

[ss]

=INDEX(A1:A65535,MAX(ROW(A1:A65535)*(A1:A65535<>"")))

Copy and paste the above formula and give F2 and press CNTRL+SHIFT+ENTER,
since it is an array formula.

Change the cell reference to your desired cell, if required.
--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------





- Show quoted text -

Hi

I have a column that contain all the values end with comma,but i want
to remove comma from last cell value only.
Please suggest me to get this.

ex:

cell A

aa,
bb,
cc,

I want to remove comma from only "cc," value

 

[Ms-Exl-Learner]

This formula will get the values which is present before the first comma.
=LEFT(A1,FIND(",",A1)-1)

This function will replace all the comma and get you the values without
comma’s.
=SUBSTITUTE(A1,",","")

Remember to Click Yes, if this post helps!

 

[ss]

This formula will get the values which is present before the first comma.
=LEFT(A1,FIND(",",A1)-1)

This function will replace all the comma and get you the values without
comma’s.
=SUBSTITUTE(A1,",","")

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------





- Show quoted text -

Hi ,
Firstly thank you so much for response.

Here i need to take off comma from only last cell in that column
(which is all other cells in that column need comma's)
I think your formula remove comma from all the cells.

please let me know if you need more explanation.

Thanks,

 

[Ron Rosenfeld]

Hi

I have a column that contain all the values end with comma,but i want
to remove comma from last cell value only.
Please suggest me to get this.

ex:

cell A

aa,
bb,
cc,

I want to remove comma from only "cc," value

In an adjacent column, enter, e.g:

=IF(ISBLANK(A1),"",IF(COUNTA(A2:$A$65535),A1,SUBSTITUTE(A1,",","")))

where 65535 is some row number below the last row you might be using.

Fill down as far as required.

You can either use this directly, or copy the values over your original.

There are other methods using VBA to do this "in situ"
--ron

 

[Ms-Xl-Learner]

Hi ,
Firstly thank you so much for response.

Here i need to take off comma from only last cell in that column
(which is all other cells in that column need comma's)
I think your formula remove comma from all the cells.

please let me know if you need more explanation.

Thanks,

Try this...

=IF(RIGHT(TRIM(A1),1)=",",LEFT(A1,TRIM(LEN(A1))-1),TRIM(A1))

If the above formula is get failed to resolve your query then pls give
some more details about your data.