Inserting a character in a field globally

  • Thread starter Thread starter coolest
  • Start date Start date
C

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