Formula to remove text string given certain conditions

[Mellin]

Hi,

Thank you so much for this forum, it has rescued my formulas so many times with a quick search of the forum posts!

I need some help please with a formula I am working on. I am trying to remove a beginning text string in a cell ("DEPOSIT ") but only if it is followed by letters and not numbers. I can figure out how to remove the text string using an IF statement, but I can't work out how to keep the text string if it is followed by number.

Here is text string examples, I want to get rid of "DEPOSIT " in the first two lines and keep it in the last two lines. The number string in the original text will always be 6 digits long.

[TABLE="class: grid, width: 1056"]
<tbody>[TR]
[TD]ORIGINAL TEXT[/TD]
[TD]DESIRED RESULT[/TD]
[TD]FORMULA[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]DEPOSIT CBA 53215522[/TD]
[TD]CBA 53215522[/TD]
[TD]IF(LEFT(A3,3)="DEP",MID(A3,9,42),A3)[/TD]
[TD]CBA 53215522[/TD]
[/TR]
[TR]
[TD]DEPOSIT AHM POW00046[/TD]
[TD]AHM POW00046[/TD]
[TD]IF(LEFT(A4,3)="DEP",MID(A4,9,42),A4)[/TD]
[TD]AHM POW00046[/TD]
[/TR]
[TR]
[TD]DEPOSIT 0004230[/TD]
[TD]DEPOSIT 0004230[/TD]
[TD]IF(LEFT(A6,3)="DEP",MID(A6,9,42),A6)[/TD]
[TD]0004230[/TD]
[/TR]
[TR]
[TD]DEPOSIT 777577[/TD]
[TD]DEPOSIT 777577[/TD]
[TD]IF(LEFT(A7,3)="DEP",MID(A7,9,42),A7)[/TD]
[TD]5777577[/TD]
[/TR]
</tbody>[/TABLE]


Any ideas?

Thank you
Mellin

 

[Aladin Akyurek]

In b2 enter and copy down:

=IF(LEFT(A2,7)="DEPOSIT",IF(ISNUMBER(MID(SUBSTITUTE(A2," ",""),8,1)+0),A2,TRIM(REPLACE(A2,1,7,""))))

 

[Mellin]

Thank you Aladin!! Exactly what I needed

 

[Aladin Akyurek]

Thank you Aladin!! Exactly what I needed

You are welcome.