If the first digit equal 7 then replace with a 6 otherwise return value

[Whu005]

Hi,

I hope someone can help answer this question.

I have data in column A like below. I want column B to look at each cell from column A. If the fist digit is a 7 replace it with a 6. If not do nothing. So the result should be as in the right hand side (B)?

A B
33109 33109
33419 33419
77003 67003
62276 62276
33653 33653
77001 67001
62281 62281
77078 67078
65992 65992


Thanks

[joeu2004]

I have data in column A like below. I want column B to look at each cell from column A. If the fist digit is a 7 replace it with a 6. If not do nothing. So the result should be as in the right hand side (B)?
A B
33109 33109
33419 33419
77003 67003
[....]

=IF(LEFT(A1,1)<>"7",A1,--("6" & MID(A1,2,99)))

Note: I assume that A1 is numeric. If it is text, omit the double-negative operation (--).

[matklt]

Hi try this in b1 and past down

=IF(INT(LEFT(A1,1))=7,INT(6&RIGHT(A1,4)),A1)

[sktneer]

Another...

Assuming your number is in A2, then

Please Login or Register  to view this content.

[Whu005]

Hi,

Thank you all that was perfect. Works great.


Cheers