Ask Your Question
1

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

asked 2020-04-24 12:10:45 +0100

Kourpetis gravatar image

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

edit retag flag offensive close merge delete

Comments

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

Opaque gravatar imageOpaque ( 2020-04-24 12:18:42 +0100 )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
Kourpetis gravatar imageKourpetis ( 2020-04-24 12:31:17 +0100 )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 "."

Kourpetis gravatar imageKourpetis ( 2020-04-24 12:36:00 +0100 )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.

Lupp gravatar imageLupp ( 2020-04-24 14:56:28 +0100 )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..

erAck gravatar imageerAck ( 2020-04-24 15:01:39 +0100 )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?

Lupp gravatar imageLupp ( 2020-04-24 15:19:39 +0100 )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.

erAck gravatar imageerAck ( 2020-04-24 16:13:53 +0100 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2020-04-24 12:35:26 +0100

Opaque gravatar image

updated 2020-04-24 14:30:11 +0100

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-04-24 12:10:45 +0100

Seen: 60 times

Last updated: Apr 24 '20