Find/Replace Question

L

Littlebear

In a column, I have a series of numbers in this format: 00700*, 01200*,
127000*. This data comes from an outside source, and I need the cells to
read 007, 012, 127, without the final 000*.

I can't seem to make this happen with Find/Replace, finding 000* and
replacing with nothing (to remove the final 000*, as I can do in Word).

Any thoughts how I might accomplish this?

Kind regards to all.
Shirley
 
C

cdb

Insert a column next to the original one (say orinal in Column A, new column
is Column B). Type into first adjacent cell in new column = left(A1,3) and
copy this to the bottom of your data. Highlight column B, copy and paste as
values, then delete Column A.
 
J

JulieD

Hi

one option is to use a helper column
say your "numbers" are in column A
in B1 type
=left(a1,3)
move the mouse over the bottom right hand corner of the cell until you see a
+ then double click, this will copy the formula down the column, now select
column B and copy it, now click on A1 and choose edit/ paste special -
values
and click ok - column b can now be deleted.

This method will keep your "numbers" as "text" .. if you want you "numbers"
to be numbers then use edit / replace - first do
find: 000~*
replace: leave blank

then do
find: 00~*
replace: leave blank.

to format your numbers back to three digits, select them and choose format /
cells - custom
type 000
and click OK

Cheers
JulieD
 
Top