We will be migrating from Ask to Discourse on the first week of August, read the details here

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.

Athanasios Kourpetis

edit retag close merge delete

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

( 2020-04-24 12:18:42 +0200 )edit

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
( 2020-04-24 12:31:17 +0200 )edit

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

( 2020-04-24 12:36:00 +0200 )edit

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.

( 2020-04-24 14:56:28 +0200 )edit

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

( 2020-04-24 15:01:39 +0200 )edit

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?

( 2020-04-24 15:19:39 +0200 )edit

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.

( 2020-04-24 16:13:53 +0200 )edit

Sort by » oldest newest most voted

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

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