REPLACE FUNCTION_ change 3.000 to 3...

[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

 

[Dave Peterson]

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).

 

[JMB]

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.

 

[Aline]

Thank you so much. It works!
--
Aline

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.

 

[Aline]

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

 

[Aline]

This could be it.

Find what : .000
Replace with :

Thank you again for your help.
Aline
--
Aline

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