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 wrote:
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

On the money Scoops, Thanks


"Scoops" wrote in message
oups.com...

John wrote:
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

Hi John

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

HTH
Regards from Brazil
Marcelo



"John" escreveu:

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 wrote:
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

Try this formula:


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

Hope it helps


--
oteixeira
------------------------------------------------------------------------
oteixeira's Profile: http://www.excelforum.com/member.php...o&userid=35320
View this thread: http://www.excelforum.com/showthread...hreadid=555618

Thanks all


"oteixeira" wrote
in message ...

Try this formula:


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

Hope it helps


--
oteixeira
------------------------------------------------------------------------
oteixeira's Profile:
http://www.excelforum.com/member.php...o&userid=35320
View this thread: http://www.excelforum.com/showthread...hreadid=555618