Trim if?

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
John said:
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
Click to expand...

Hi John

Try:

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

Regards

Steve
 
Hi John

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

HTH
Regards from Brazil
Marcelo



"John" escreveu:
 
John said:
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
Click to expand...

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
 
Back
Top