Delete Last Character if its an 'N'

I have a column of data.

Some end in 'N' some don't.

For the ones that end in 'N' I would like to remove the 'N'.

Find and replace wouldnt work as some of them have an 'N' at the start or in the middle.

How can I removed the last charcter if it is an 'N'?

Thanks.

Try

=LEFT(A1,LEN(A1)-(RIGHT(A1)="N"))

note this isn't case-sensitive, if you want to remove "N" but not "n" amend to

=LEFT(A1,LEN(A1)-(EXACT(RIGHT(A1),"N")))

I have a similar situation as above, except i need to replace any word ending in "S" with "v" for a column. How would you write the formula to replace rather than just delete?

oldchippy, you're a genius. thanks!

Another way....

=LEFT(A1,LEN(A1)-1)&SUBSTITUTE(RIGHT(A1),"S","v")

Thanks oldchippy and daddylonglegs for your responses. Here's another one I'm wrestling with:

I have a database where characters were encoded with a '*' before a letter to indicate that that letter is capitalized. I need to search all cells that begin with '*' and then capitalize whatever letter comes after the *, finally to delete the *.

I.e. *j --> J

Hi

Try

=IF(LEFT(A1,1)="*",UPPER(MID(A1,2,1))&RIGHT(A1,LEN(A1)-2),A1)


rylo

Rylo,

I get the #NAME? error when doing your suggestion. Here's the example of what I was looking for (I think you understand but I gave a poor example above).

A B
1 *dog Dog
2 *cat Cat
3 *bird Bird

Thanks!

Hi

Do you have the UPPER function available? If you put the formula
=UPPER("p")
in a cell, does it error? If so, then you will need to bring in the relevant addin.

If that is OK, then can you put in
Find and correct errors in formulas
in the help bar, and work through the "calculate a nested formula ..." steps to see which part of the formula is giving the error.

rylo

Sorry, Rylo, your formula does work! There was a space in the formula which is what caused the error. Thanks for your suggestion!

Hi,

I have a column of alphanumeric data. Some cells end in a period (.) while others don't. I want to delete the periods that are at the end of the cells but not the periods that might be found throughout the rest of the cells. I can't seem to figure out the proper formula for doing so. Any help would be much appreciated. Thanks!

For a dynamic Excel spreadsheet I want to use for my at home analysis, Stock Option Ticker JNJ170120C00125000 needs to be converted into .JNJ170120C125

In other words, delete certain zeros and add a period at the beginning.

This probably could be done more efficiently, but I accomplished this by some formulas I found on the internet and adapted:

=MIN(FIND({"000"},K15&"000")) returns position 16, then

=REPLACE(K15,AV15,3,"") where AV15 is position 16 returns JNJ170120C00125 as AW15 then

=MIN(FIND({"00"},AW15&"00")) returns position 11 as AX15 then

=REPLACE(AW15,AX15,2,"") returns JNJ170120C125 as AY15 then

=CONCATENATE(".",AY15) puts in the leading period and returns .JNJ170120C125 which is what I want.


HOWEVER, there are other Option Symbols which need to be automatically converted slightly differently, ie:

ETN170120C00067500 needs to be converted to .ETN170120C67.5

and ETN170120C00065000 needs to be converted to .ETN170120C65


My initial formulas shown above return .ETN170120C675 for the first one and .ETN170120C650 for the second one.


I would like to substitute .5 for 5 at the end of the string for the first one, and delete the final 0 in the second string without altering the result correctly obtained for JNJ above. Any suggestions?

JNJ and ETN are 3 characters, but some of the option symbols can have 4 characters or 1 or 2 characters, so we do have to find the position of what we want to change, which can move around. Also, there can be other 0 and 5 in the string, so it's important that the final changes only apply to the last character in the string after it has been truncated as accomplished by my formulas above.

Finally, hopefully these formulas will work through all the permutations of Option Symbols, but that will become better known after I can correctly convert ETN.

Quite possibly there is a more straightforward way than my formulas, plus more formulas I am kindly requesting. If so, please don't hesitate to correct or streamline my initial formulas. Otherwise, some additional formulas to add to mine to get the correct result for ETN while not altering JNJ result will be much appreciated. The goal is to be able to insert the unconverted option symbol and have the formulas return the converted option symbol dynamically.

I'm not good at VBA programming but if there is something I can just copy and paste, this would be great as an alternative. I will need some help copying and pasting, and activating the VBA, as I haven't done that in a long time.

Hopefully I have been clear in the explanations. Thanks very much in advance.