Change a letter and delete digits in a cell

[harriet60]

HI,

Please bare with me - I'm new to the forum and learning excel later in life

I have columns of data that follow a similar pattern (always 4 letters to start, followed by a series of numbers).
I need to change the 4th letter (regardless of what it is) to another letter (always the same one).
I also need to remove the first 4 numbers.

I have enclosed an example but basically I need to change ABCD12013456 to ABCR3456.
The letters will always consist of 4 but the numbers may vary - however, it is always the first 4 I need to remove.

Thanks in advance for your help.

 

[Joe4]

Welcome to the Board!

Try this for a value found in cell A1:

=LEFT(A1,3) & "R" & MID(A1,9,LEN(A1))

 

[Fluff]

Another option

=REPLACE(A2,4,5,"R")

 

[harriet60]

Thank you - it works perfectly.

Very grateful.

 

[harriet60]

Welcome to the Board!

Try this for a value found in cell A1:

=LEFT(A1,3) & "R" & MID(A1,9,LEN(A1))

Thank you - it works perfectly - very grateful!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[harriet60]

Morning Everyone,

Is it possible to add an 'IF' function to this please?

In another column I have a list of cities. I want to state if cell = MADRID, apply the formula, if not, return the original number.
I've tried IF(F17="MADRID",LEFT(G17,3) & "R" & MID(G17,9,LEN(G17),G17)) but the response is 'too many arguments'.
Screenshot updated.

 

[Fluff]

How about

=IF(F17="Madrid",REPLACE(G17,4,5,"R"),G17)

 

[harriet60]

Thank you so much - it works

 

[Fluff]

My pleasure.