REPLACE FUNCTION_ change 3.000 to 3...

  • Thread starter Thread starter Aline
  • Start date Start date
A

Aline

There's a column with results (1.000, 2.000, or 3.000, the Format Cells... is
Text) I would like to replace with 1, 2 or 3.

Is there a easier way to do it? I have tried =REPLACEB(A1, 2,4," "), but
it's not working. In the end I had to use Find function : find 3.000 and
replace 3; find 2.000 and replace 2, find 1.000 and replace 1.
(I've also tried change Format Cells from Text to General, and Number...)

A B
Level Obtained Level
3.000 3
2.000 2
2.000 2
3.000 3
1.000 1

Thanks,
Aline
 
Select column A
Format as General (anything but Text).
Edit|Replace
what: . (decimal point)
with: .
replace all

Excel should see the value as a number and will display 3.000 as 3 (in General
format).
 
another possibility - just in case not all of your data has a decimal so you
can use find/replace.

select the column in question, change the format of the cells to general.
with your column still selected, click data/text to columns, select
delimited, click next, uncheck any delimiter options that may be checked,
click next, select general under the format options, click finish.
 
Thank you so much. It works!
--
Aline


JMB said:
another possibility - just in case not all of your data has a decimal so you
can use find/replace.

select the column in question, change the format of the cells to general.
with your column still selected, click data/text to columns, select
delimited, click next, uncheck any delimiter options that may be checked,
click next, select general under the format options, click finish.
Click to expand...
 
Hi Dave,

Thank for your response. I tried but a pop-up says: Microsoft Excel cannot
find matching data to replace...

I also tried with other things but not working.
what: *.*** (decimal point)
with: *.*** (and also . )

Any suggestions on how to fix this problem.

Thanks,
Aline
 
This could be it.

Find what : .000
Replace with :

Thank you again for your help.
Aline
--
Aline


Aline said:
Hi Dave,

Thank for your response. I tried but a pop-up says: Microsoft Excel cannot
find matching data to replace...

I also tried with other things but not working.
what: *.*** (decimal point)
with: *.*** (and also . )

Any suggestions on how to fix this problem.

Thanks,
Aline
Click to expand...
 
Back
Top