How can i search if there is . on a text and if not to add it at the 3 place of the cell ?
I have a list of coordinates but some of them does not have (.) in them. example :
45.5689 459678 45856 44.85697 12.5687 158697
so all i need to do is to search in each cell if there is the (.) and if not to go and add it in the 3 place of the cell.
i did find a way to form them and make the cells red, in order to go and do it manually, but we are talking about thousands of cells, so i am looking for a more automated way of doing it .
all cells have text inside and not number.
Looking forward for your reply.
thank you for your time reading it
Athanasios Kourpetis
What does that mean? Your 6 data above on show part of the respective cell's content, e.g. the realo content is looking like
sometext1 45.5689
,sometext2 459678
...sometext6 158697
? Or does it mean that your data above are already text (through formatting your cells as text before adding the data)?the above data are cells. Late me make it better in order to understand.
the above are cells with text, the numbers that you see is text actually. the are a column of cells one right under the other. the data that you have is for each cell :
i found this : link text
but i don't know how to make it work with ( search if there is "." in the cell ) and if the replace will go and replace the 3rd char in the text ( witch is a number and we need it anyway ) with a "."
Are you (everybody) aware of the fact that geographic coordinates in degrees (°) may well need only 1, but also (longitudes) up to 3 places for the integer part.
I would consider to trash the bad values and replace them by reliable ones.
You will not find good recipes for solving such data replacement problem on any Excel related site because Excel does not know regular expressions. Of course one could use cumbersome formulas like
in a helper column and maybe even add logic for cases where content is not a number, but..
Quoting @Kourpetis; "all cells have text inside and not number."
BTW: One value (4.) has 7 digits disregarding the point.
All theMost other values have 6. OK?I missed that "text inside" and the
from the above comment, so whatever, if the content was text and a cell is formatted as text then the result of a Find&Replace will re-enter text as well.