How can i search if there is . on a text and if not to add it at the 3 place of the cell ?

[Kourpetis]

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

[anon73440385]

all cells have text inside and not number.

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

[Kourpetis]

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 :

1. 45.5689
2. 459678
3. 45856
4. 44.85697
5. 12.5687
6. 158697

[Kourpetis]

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 “.”

[Lupp]

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.

[erAck]

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

=NUMBERVALUE(IF(ISERROR(SEARCH(".";A1));REPLACE(A1;3;0;".");A1);".")

in a helper column and maybe even add logic for cases where content is not a number, but…

[Lupp]

Quoting @Kourpetis; “all cells have text inside and not number.”

BTW: One value (4.) has 7 digits disregarding the point. All the Most other values have 6. OK?

[erAck]

I missed that “text inside” and the

the numbers that you see is text actually

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.

[anon73440385]

Hello,

thanks for the clarification and now I understand your question that you finally want it look like this:

1. 45.5689
2. 45.9678
3. 45.856
4. 44.85697
5. 12.5687
6. 15.8697

If my understanding is correct use the following procedure

• Select your column
• Menu Edit -> Find & Replace
  Find: ^(\d{2})(\d+)
  Replace: $1.$2
  Other Options 1: [x] Current selection only
  Other Options 2: [x] Regular expressions
• Click Replace All

If you want your original data leave untouched and work with an additional column, you may use:

=REGEX(A1,"^(\d{2})(\d+)","$1.$2")
=REGEX(A2,"^(\d{2})(\d+)","$1.$2")
… and so on (assuming the data are in column A and start in row 1)

Note: Funtion REGEX() appears starting with LibreOffice 6.2

Tested using LibreOffice:

Version: 6.4.3.2; Build ID: 747b5d0ebf89f41c860ec2a39efd7cb15b54f2d8
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US; Calc: threaded

Hope that helps.

If the answer helped to solve your problem, please click the check mark () next to the answer.