Replace string, if it exists

[nick75]

Hello,

I pull in some data into Excel from a website and I then end up with a column similar to this:

Column I
French Opera 2/1
Chaninbar (FR) 33/1
Cornas (NZ) 15/2
Oiseau De Nuit (FR) 4/1
Tataniano (FR) 6/4F
Russian Flag (FR) 20/1

I would like to strip off the trailing letter if it exists. Sometimes one of the entries will have a trailing "F", as above. At other times one or more entries will have a trailing "C" or "J". The number of rows I have varies each time I pull in the data. Also, each of the above entries has a trailing blank space.

Could anyone help me to strip off the right-most letter if it exists in any of the rows, please?

Many thanks in advance.

Nick

 

[Aladin Akyurek]

Hello,

I pull in some data into Excel from a website and I then end up with a column similar to this:

Column I
French Opera 2/1
Chaninbar (FR) 33/1
Cornas (NZ) 15/2
Oiseau De Nuit (FR) 4/1
Tataniano (FR) 6/4F
Russian Flag (FR) 20/1

I would like to strip off the trailing letter if it exists. Sometimes one of the entries will have a trailing "F", as above. At other times one or more entries will have a trailing "C" or "J". The number of rows I have varies each time I pull in the data. Also, each of the above entries has a trailing blank space.

Could anyone help me to strip off the right-most letter if it exists in any of the rows, please?

Many thanks in advance.

Nick

J2, just enter and copy down:

=TRIM(IF(ISNUMBER(MATCH(RIGHT(I2),{"C","F","J"},0)),LEFT(I2,LEN(I2)-1),I2))

 

[nick75]

Thanks Aladin, but would it be possible to modify the cells in place using VBA?

 

[MickG]

Try :-

[COLOR="Navy"]Sub[/COLOR] MG12Jun46
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Right(Trim(Dn), 1) Like "[a-zA-Z]" [COLOR="Navy"]Then[/COLOR]
        Dn = Left(Trim(Dn), Len(Trim(Dn)) - 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[nick75]

Thanks Mick, that worked to perfection. Much appreciated.

Best,
Nick