Trim if?

[John]

How can I TRIM i.e. remove the first 3 numbers in a column of data (all
rows) if the leading 3 numbers are 123, and replace them with 9

so for example: if my existing number is 123987654 replace with 9987654

Thanks

 

[Scoops]

How can I TRIM i.e. remove the first 3 numbers in a column of data (all
rows) if the leading 3 numbers are 123, and replace them with 9

so for example: if my existing number is 123987654 replace with 9987654

Thanks

Hi John

Try:

=IF(LEFT(A1,3)="123",9&MID(A1,4,LEN(A1))*1)

Regards

Steve

 

[John]

On the money Scoops, Thanks

Hi John

Try:

=IF(LEFT(A1,3)="123",9&MID(A1,4,LEN(A1))*1)

Regards

Steve

 

[Guest]

Hi John

try =9&right(a3,len(a3)-3) and copy it down

HTH
Regards from Brazil
Marcelo



"John" escreveu:

 

[Scoops]

How can I TRIM i.e. remove the first 3 numbers in a column of data (all
rows) if the leading 3 numbers are 123, and replace them with 9

so for example: if my existing number is 123987654 replace with 9987654

Thanks

Or

=IF(LEFT(A1,3)="123",9&MID(A1,4,LEN(A1))*1,A1)

to retain the original value if it doesn't start "123"

Regards

Steve

 

[oteixeira]

Try this formula:


=IF(LEFT(H17,3)="123",MID(H17,4,LEN(H17-3)),H17)

Hope it helps

 

[John]

Thanks all