Formula for Extracting the Last Character in Each Cell - Please Help!

[walle786]

Hello,

I have a list with over 5K+ rows of data. I am trying to extract the very last character from each cell for 3 particular rows. Here is the formula that I tried using; it is giving me the last text/word, and I am at wit's end as to how to fix it:


=RIGHT(T347, LEN(T347) - FIND("*", SUBSTITUTE(T347, " ", "*", LEN(T347) - LEN(SUBSTITUTE(T347, " ", "")))))

Any help would be greatly appreciated!

Wally

[vinodhec]

Right(T347,1)

[FDibbins]

Hi and welcome to the forum

To return the very last character, all you need is =right(T347,1)

Im not sure how the "3 particular rows" fits inthough?

[FDibbins]

deleted my duplicate post

[walle786]

Thank you!

I did try that, but it seems that I have hard spaces at the end of the cells, and doing Right(T347,1) alone doesn't capture the last character. Is there a way to get rid of the spaces at the end of each cell and then extract the last character?

Thank you all once again.

[walle786]

Thank you!

I did try that, but it seems that I have hard spaces at the end of the cells, and doing Right(T347,1) alone doesn't capture the last character. Is there a way to get rid of the spaces at the end of each cell and then extract the last character?

Thank you all once again.

[Sanish Tirkey]

Try =RIGHT(TRIM(T347),1)

[walle786]

Unfortunately it is not working. I get an error. Also, I sometimes have more than 1 space at the end. Is there a < or > option to fix this?

Thank you!

[walle786]

I'm sorry - it does work, but what if there is more than 1 space? Can I do =RIGHT(TRIM(T347), <>1) or something of the sort?

[Sanish Tirkey]

Can you post one data item, so I can check. As TRIM should remove all the leading and trailing spaces in any cell.

[walle786]

Hi,

Please see attached. If there is more than 1 space, then the formula doesn't work (check celll D1). Thank you!

[Sanish Tirkey]

Your value at B1 has an "Enter" charater at the end, which is not removed by TRIM. If this is a one time activity you are working on, they can be removed by a custom function. I will try and see if any of the existing excel formulas can do that.

[walle786]

Thank you! What about the formula I have on top?

=RIGHT(T347, LEN(T347) - FIND("*", SUBSTITUTE(T347, " ", "*", LEN(T347) - LEN(SUBSTITUTE(T347, " ", "")))))

If the last word in a cell is "go", it will give me that last text. I don't have any issues with spaces with this function. Is there a way to add to the function so it only gives the last character?

[Sanish Tirkey]

=RIGHT(TRIM(REPLACE(B1,FIND(CHAR(10),B1,1),1,"")),1)

Solved . But this will only work with one ENTER character. I would like to see a more dinamic one though.

[benishiryo]

@Sanish Tirkey:
use SUBSTITUTE instead of REPLACE.
=RIGHT(TRIM(SUBSTITUTE(B1,CHAR(10),"")))

and what you mentioned is an ALT + ENTER (a carriage return). not sure if this is what OP wants though. it returns the last bracket.

@walle786:
you have to give us a bigger sample & show your desired results to let us know what you roughly need. if you don't want close brackets, then:
=RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE(B1,CHAR(10),""),")","")))

but we can't possibly foresee what you are facing. your formula does not retrieve the last character. it retrieves everything after the last space

[walle786]

@Sanish Tirkey:
use SUBSTITUTE instead of REPLACE.
=RIGHT(TRIM(SUBSTITUTE(B1,CHAR(10),"")))

and what you mentioned is an ALT + ENTER (a carriage return). not sure if this is what OP wants though. it returns the last bracket.

@walle786:
you have to give us a bigger sample & show your desired results to let us know what you roughly need. if you don't want close brackets, then:
=RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE(B1,CHAR(10),""),")","")))

but we can't possibly foresee what you are facing. your formula does not retrieve the last character. it retrieves everything after the last space

Hi!

Sorry for the late reply. I can't show the actual data due to the personal content, but here is a great sample. It encompasses the same issues that I am having - that is, the carriage return (ALT + Enter). I tried the formulas above, and they get close, but I don't always get every character. With 5K rows, I will never catch them manually.

Also, as a separate question, if you do a VLookup and want the values to be copied over to a new column, how do you do this without inputting data in other cells? I have a filter on so the Vlookup skips hundreds of cells, and when I try to copy and paste the values, it enters them into the incorrect cells.