****Calc Find** and **Replace****, well maybe **Place** Phone & Addresses might be a better word for it

[MarkC1]

Calc Find and Replace, well maybe Place might be a better word for what I need to do, maybe using ctrl + h.

I was thinking maybe Find and Replace all characters and spaces BEFORE any given character, i.e. before a “(“; and delete everything AFTER any given amount of numbers, spaces or characters.

Sorry, I’m totally lost here. Any help you can give will surely be much appreciated.

Thanks a lot for your help ahead of time.

Here is an example of what I’m trying to do:

The following are 4 cells in a column of 100 cells or more; a list of phone numbers and addresses and many pages of the same. This is the way I get the list from the source. It’s a mess.

If possible, I like to have the phone numbers, (i.e. (614) 421 8714 separated like this), in a column first, then the person’s full name follows by the addresses in another column. The whole address would be good to have in one cell by itself.

Note: Here is how addresses work in Mexico. This comes next…

Street name: Chichontepec then the…

The number on the house: 6152

Zip Code: (31107) without () The zip always comes after the house number.

The colony or sometimes called Fracc: and the name of the Fracc or colony Panoramico,

And finally the

State: Chihuahua

Country: México

Yes, the city normally comes after the state like this Chihuahua, Chihuahua, then Mexico.

In the case of this list, they didn’t put the name of the city. I guess they figured we all know that anyway.

I’d like to extract all the phone numbers, (i.e. (614) 4218714), from each cell in the column and place them exactly to the left of that column.

Madrid Madrid Margarita(614) 4218714Chichontepec 6152(31107) Fracc Panoramico, ChihuahuaMéxico

Mancinas Peña Sergio(614) 4198920Chichontepec 6138(31107) Fracc Panoramico, ChihuahuaMéxico

Marin Garcia Ma De Lour(614) 4218594Chichontepec 5708(31107) Fracc Panoramico, ChihuahuaMéxico

Marrufo Teresa(614) 4216196Chichontepec 6119(31107) Fracc Panoramico, ChihuahuaMéxico

For me, my head is just spinning around just thinking how to go about doing this.

All I can say is thank you for having all those brilliant young men and women (and maybe some not so young) up there in the cloud.

Thanks one more time.

Looking for a speedy recovery of my brain right now. (I’m 70) Sorry about giving you my life’s history.

[JohnSUN]

I tried it in a hurry - this works for me

=REGEX(A2;"\(\d{3}\)[ |\d]*")
=LEFT(A2;FIND(B2;A2)-1)
=SUBSTITUTE(A2;C2&B2;"")

[MarkC1]

Wow, JohnSUN I can see why they call you the leader. That was fast work! You really know your stuff.

Just another thing if I may ask. Right now I’m in ctrl + h. I don’t know where to put those lines you wrote. Do I put them all together at the same time on the Find line? Do we need to put anything in the Replace line? Do I need to put a check in Regular expressions? Do I need to highlight all the rows first?

Sorry about being so incapable when it comes to these coding things. If you could help me understand.

Thank you JohnSUN. I wish to follow your lead.

MarkC1

P.S.
Say, JohnSUN, Are you in the Ukraine? If you are our hearts go out to you. That’s really terrible what is happening over there. This world has gone mad/crazy. I hope everything is OK for you and your family.

[JohnSUN]

My dear friend! Your words of support are very important to me! Yes, I’m in Kyiv, the capital of Ukraine. And no, the world has not gone mad. Against! The world woke up from the insane indulgence of a terrorist maniac.

I suggested a way that doesn’t use CTRL+H (Find And Replace). These are formulas that you need to enter in the cells of your table next to the “raw” data column. I assumed that the texts to be parsed are in column A. If so, then you can use the formulas from my answer without any modifications. Just select cell B2 and enter (or paste) the first of the formulas. Go to cell C2 and paste the second formula. Repeat for cell D2 and the third formula.
Now make sure that the cells in which you entered the formulas display the correct fragments of the “raw” line. If everything is correct, then select cell B2 again, press Ctrl + Shift + End (all cells in columns B, C and D will be selected until the end of your data) and press Ctrl + D (this will multiply the formulas into all selected cells). Just try it, I’m sure you’ll succeed!

[MarkC1]

JohnSUN here’s what I did.

Pasted in cell B2 =REGEX(A2;"(\d{3})[ |\d]*")
Pasted in cell C2 =LEFT(A2;FIND(B2;A2)-1)
Pasted in cell D2 =SUBSTITUTE(A2;C2&B2;"")

as the pictures that I sent shows below.

That seemed to be your instructions and I did exactly that, even though it looked like (from your image) that you put the formulas in cells B1, C1, D1. The results came back in each cell like this . . .
#N/A =LEFT(A2;FIND(B2;A2)-1) #N/A

It didn’t separate correctly as we were expecting.

Also, once I put each formula in cells B1, C1, D1 and selected all cells, then Ctrl + D, and the results came back the same way
#N/A =LEFT(A2;FIND(B2;A2)-1) #N/A.

Now, even though in the cells it looked like this
#N/A =LEFT(A2;FIND(B2;A2)-1) #N/A

In the Input Line, each cell showed correctly like this . . .

=REGEX(A2,"(\d{3})[ |\d]*") =LEFT(A2;FIND(B2;A2)-1) =SUBSTITUTE(A2,C2&B2,"")

There was one thing I didn’t understand when you said . . .

To make sure that the cells in which you entered the formulas display the correct fragments of the “raw” line. ???

What do the correct fragments of the “raw” line mean? Does it mean showing like this? . . .

=REGEX(A2,"(\d{3})[ |\d]*") instead of this #N/A. Only the second cell was correct that way.

I can’t figure out why, when I pasted each formula exactly and directly into the cells two of them came out like this #N/A.

JohnSUN what did I do wrong?

Thanks for your continued help

[MarkC1]

[MarkC1]

[MarkC1]

I would also like to mention that I am using Linux Mint 20.3 and have the latest version of the LibreOffice suite.

[JohnSUN]

Oh, in your pictures, the original data is in column E, I did not expect this. Please see this -
Substrings.ods (24.5 KB)

[MarkC1]

Sorry I should have sent you a picture in the first place
And if its not too much trouble could you separate out the phone #s like this…
(614) 419 8920 instead of like this
(614) 4198920

Thank you

Say, JohnSUN how is it that you write so good English anyway?

[JohnSUN]

@MarkC1

(614) 419 8920 instead of like this
(614) 4198920

Yes, it can be done, but later. This will be done with a simple function like
=REGEX(B2;"(..... ...)(....)";"$1 $2")
or some other. But I advise you to postpone this action until the very end of the data parsing. The fact is that if this is done immediately, then the second formula will not be able to find the desired substring and will give an error, and because of this, the third formula will also break. Therefore, I suggest moving step by step, not trying to get everything at once. First, a rough parsing of the lines, then correcting possible errors, and at the very end formatting the results in the desired form (agree that the presence or absence of a space in the phone number is just formatting, appearance)

@MarkC1

Say, JohnSUN how is it that you write so good English anyway?

I’m sorry I didn’t understand this question

By the way, where did you get this data in such an inconvenient form? From some site? From some file? Maybe if you CORRECTLY insert this data into the spreadsheet (CORRECTLY open this file), then all the data will be correctly distributed into individual cells and you will not need any formula? I have no doubt that you did everything in the only possible way, but still I must clarify.

[Gyula]

I’m very happy your answer. I wrote a letter yourname at i dot ua. Are you use it ?

[mikekaganski]

@MarkC1:

@MarkC1

who has been helping me all along?

You ask a strange question. Whose messages were helping you all along? @JohnSUN’s, and he was the one you were talking with. You saw his profile.

Then, at some point, @Gyula replied with an unclear post (not telling whom they answered, and which specific “answer” they were talking about when expressed their happiness). And you started to confuse the person with different nickname, and different profile (where it’s mentioned that they are from Hungary), with @JohnSUN. Do you also confuse people that easily when you are talking in person, and someone passing by inserts a word or two?