Change any first digit to 1?

=--("1"&MID(A1,2,255))

The -- converts the string back to a number.
The 255 is a number that's big enough to cover the rest of the string.

bill wrote:

Thanks thats what i was looking for!

One other question..

how do i eplace a number?

for example after i removed the unwanted digits how do i replace say the
first number "2" with a number "1"

E.G Change 23456 to 12345...

Many thanks!

Bill

"Ron Coderre" wrote:

Perhaps Text-to-Columns?

If all cell values are 12 characters in length
try something like this:

Select the single-column range of cells

Then...from the Excel main menu:
<data<text-to-columns
Check: Fixed width..........click [Next]
Click between the 2nd and 3rd characters (to insert a break point)
Click between the 9th and 10th characters (to insert another break point)
Click [Next]
Click on the 1st of the 3 columns....Check: Do not import column
Click on the 3rd of the 3 columns....Check: Do not import column
Click [Finish]

That will lop off the 1st 2 and the last 3 characters

(BTW....Your example of H016690180 only has 10 characters, not the 12 you
indicated in your first post.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"bill" wrote:

thanks for the response david.

let me explain a little further..

For example... currently I have the following in cell A1... H016690180

I wish to remove the H0 from the beginning and the 180 from the end....

Is this possible to have excel perform this automatically?

thanks again,

Bill

"David Biddulph" wrote:

=MID(A1,4,7) and copy down.
--
David Biddulph

"bill" wrote in message
...
Hi,

i'm not sure how to explain this but here goes...

i have cells containing 12 digit numbers. I need to remove the first 3 and
the last 2 digits. As there are 1000 plus cells (all in the same colum)
which
i need this to occur is there a way to automate this inexcel?

thanks,

Bill




--

Dave Peterson