Inserting a character in a field globally

[coolest]

I have a field that contains a 7-digit number. I would like to insert
9 in the fourth place (that is, between the existing third digit an
the fourth digit) in order to create an 8-digit number. Is there
function that will allow me to do this either in the existing column o
in a new column adjacent to the existing column?

Example:

Existing field/column: 0010050

Desired result: 00190050

Many thanks--

Ti

 

[Peo Sjoblom]

One way using a help column

=SUBSTITUTE(A1,LEFT(A1,3),LEFT(A1,3)&9,1)

 

[Kieran]

if your number is really text as post seems to indicate then use
=LEFT(A1,3)& "9" & RIGHT(A1,4) (where a1 contains the source text)

if it is a number then try
=INT(A1/10000)*100000 + 90000 + A1-(INT(A1/10000)*100000)

 

[Bernard Liengme]

Hi Tim,
If you really have a number then =INT(A1/10000)*100000+90000+MOD(A1,1000)

But with the leading zeros, I suspect you have a text with a string of
digits. In that case =LEFT(A1,3)&9&RIGHT(A1,4)

 

[coolest]

Hello, Peo, Kieran, and Bernard:

You all suspected correctly: the "number" I referred to is actually i
a text field.

The formula each of you provided for the text field worke
wonderfully!

Many many thanks--

Ti