[Solved] Search and replace part of string with increment

Discuss the spreadsheet application

[Solved] Search and replace part of string with increment

Postby Fredrik Nilsson » Fri Mar 29, 2019 11:01 am

This is what I want to achieve!
Content in column A to be modified according column B.
(Only the value marked red)

Is it possible?
Maby a logical funktion or similar.

Would be very greatful for a solution! ;)
A file is attached.

/Fredrik
Attachments
2.ods
(12.1 KiB) Downloaded 15 times
1.JPG
Last edited by Fredrik Nilsson on Fri Apr 05, 2019 4:43 pm, edited 1 time in total.
Apache openoffice 4.1.1
Windows 10
Fredrik Nilsson
 
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am

Re: Search and replace part of string with increment

Postby MrProgrammer » Sat Mar 30, 2019 9:54 pm

Hi, and welcome to the forum.

Fredrik Nilsson wrote:Content in column A to be modified according column B.
Attachment: 2.ods
I will presume the goal is to number the OPERATION # cells in column A by tens and that none of the EXAMPLE TEXT cells begin with an open parenthesis. Select cell A2, Insert → Names → Define → Name → _ → Assigned To → $Ark1.$A$1:$Ark1.$A1 → Add → OK (with five $ in the Assigned To value as shown).

Select the A2:A24, Edit → Find & Replace → More options, select Current selection only and Regular Expressions, Search for ^\(.+$, Replace with =CONCATENATE("(OPERATION # ";10+10*SUMPRODUCT(LEFT(_;1)="(");")"), Replace All. I recommend unchecking Current selection only and Regular expressions, before clicking Close. The Search For text is six characters.

If you like you can then replace the formulas with their values. Select A2:A24, Edit → Copy, Edit → Paste Special → Paste All → Text → Formulas → OK. Or you can leave the formulas and when you want a new OPERATION # cell copy the formula in A2 to that cell.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3815
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Search and replace part of string with increment

Postby Fredrik Nilsson » Sun Mar 31, 2019 10:49 pm

Hi MrProgrammer!

Thanks a lot!
You got the mission completley right. The file with text example is a little part of a long code for a cnc-machine.
But I'm sorry to say I didn't succeed.
Please have a look at the attached pictures 1-6.
I tried to follow your instructions carefully but I must have missed something.
(My openoffice calc is with Swedish langue)
Apache openoffice 4.1.1
Windows 10
Fredrik Nilsson
 
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am

Re: Search and replace part of string with increment

Postby Hagar Delest » Mon Apr 01, 2019 8:02 am

There are some limitations to the number and size for upload. You can use a 3rd party file sharing web site instead.
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28543
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Search and replace part of string with increment

Postby MrProgrammer » Mon Apr 01, 2019 4:18 pm

Fredrik Nilsson wrote:Please have a look at the attached pictures 1-6.
As you can see, you did not successfully attach any pictures. However, instead of pictures, it will be far more useful to us if you attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply). If your document is large, make a copy of it, then delete all but the first 50 or so lines, save as a new name, say ForumAttachment.ods, after adding the defined name and running Find&Replace, and attach that. One document will probably tell us more than six pictures. If you prefer you could attach one document after adding the defined name and a second document after Find&Replace.

Fredrik Nilsson wrote:(My openoffice calc is with Swedish langue)
My formula is written for the English (USA) locale, as explained in my signature line below. You need to use the localized function names for Swedish, CONCATENATE=SAMMANFOGA, SUMPRODUCT=PRODUKTSUMMA, and LEFT=VÄNSTER, I believe. Insert → Function List should show you the correct spelling of the function name. In my function list, CONCATENATE and LEFT are a text functions and SUMPRODUCT is an array function. Calc will produce a #NAME! error for your formula if a function name is misspelled.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3815
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Search and replace part of string with increment

Postby Fredrik Nilsson » Tue Apr 02, 2019 10:50 pm

Ok sorry about no pictures attached.
Now two diffrent documents regarding my previous example is attached.
I have changed the function names to swedish (3 words) but with no success.

I hava also attached an other document (Forumattachment 3) that is similar to my first example but I want to replace the value with a increment of 1 instead of 10 in every "( OPERATION # _ )"
The cells that I don't want to change is more complexed than my first example.

Thanks for helping me with this issue!
B/R
Fredrik
Attachments
ForumAttachment 3.ods
Increment 1 instead of 10
(21.72 KiB) Downloaded 10 times
ForumAttachment 1.ods
With Find&Replace
(12.84 KiB) Downloaded 9 times
ForumAttachment 2.ods
(13.03 KiB) Downloaded 8 times
Apache openoffice 4.1.1
Windows 10
Fredrik Nilsson
 
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am

Re: Search and replace part of string with increment

Postby MrProgrammer » Wed Apr 03, 2019 10:28 pm

Fredrik Nilsson wrote:I have changed the function names to swedish (3 words) but with no success.
In ForumAttachment 1.ods, you receive Err:522 because when cell A2 is selected, Insert → Names → Define → Name → _ shows $Ark1.$A$1:$Ark1.$A2 instead of $Ark1.$A$1:$Ark1.$A1. You did not follow the directions. The solution I gave will not work if the defined name is built incorrectly.

Fredrik Nilsson wrote:I want to replace the value with a increment of 1 instead of 10
Well, replace 10 with 1 in the formula, of course. If you need additional assistance, open a new topic.

Fredrik Nilsson wrote:ForumAttachment 2.ods
This attachment shows a significant difference from ForumAttachment 1.ods and from 2.ods. The latter two have an empty row as row 1. In ForumAttachment 2.ods, the data begins in row 1. The instructions I gave are specifically for the case which you originally provided where row 1 is empty, and those instructions will not work if row 1 has data. Either add an empty row at the top or open a new topic to discuss this different situation. I seldom help in a topic if the situation changes after I have responded with a solution.
Screen Shot 2019-04-03 at 17.16.17 .jpg

Fredrik Nilsson wrote:in every "( OPERATION # _ )" … The cells that I don't want to change is more complexed than my first example.
ForumAttachment 3.ods shows the data beginning in row 1 and that the formatting is not consistent. Row 15 has a space between the opening parenthesis and OPERATION. Rows 89, 141, 163, etc. do not have a space. It will be simpler if the formatting is consistent. An easy way would be to remove the space to the left of OPERATION in row 15. Otherwise, open a new topic.

If you need this to work with inconsistent formatting, you will need to understand regular expressions, and the Replace With expression may become more complex. As far as I am concerned, the situation described in your original post is solved. If you need further help with inconsistent formatting of the OPERATION # cells or with regular expressions, that is a new topic, and you will have to explain precisely how Calc is going to be able to identify the inconsistent contents which identify the OPERATION # cells. Posting an example and expecting someone else to determine your requirements is not a good plan.
Documentation/How Tos/Regular Expressions in Calc
Documentation/How Tos/Regular Expressions in Writer

MrProgrammer wrote:… and that none of the EXAMPLE TEXT cells begin with an open parenthesis
Unlike your original post, the data in ForumAttachment 3.ods no longer meets this requirement. I could have designed a solution for the new situation, but it would have been more complex, and there was no reason for me not to give a simple solution. One issue, one thread

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3815
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Search and replace part of string with increment

Postby Lupp » Thu Apr 04, 2019 2:03 am

aoo#SpecialIncrement#3.ods
(59.86 KiB) Downloaded 14 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search and replace part of string with increment

Postby Fredrik Nilsson » Fri Apr 05, 2019 4:27 pm

Hi Mr Programmer!
Thanks for the help.
Regarding Cell A1. That was an issue I had about the last number in cell formula $Ark1.$A$1:$Ark1.$A1. It changed to 2 and even to other number without my control. The attachment I posted, last nr i formula was cell 1 when I saved the document but if I open again it was nr 2. (Cell A1 was selected when I defined name "_". However I still cant get it working.
Sorry for the inconsistent documents. My misstake. I will check this better futher on.
Apache openoffice 4.1.1
Windows 10
Fredrik Nilsson
 
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am

Re: Search and replace part of string with increment

Postby Fredrik Nilsson » Fri Apr 05, 2019 4:36 pm

Hi Lupp and Thanks!

This works excellent! :D
I have already corrected a couple of cnc codes with about 10000 lines.
Also good explanations in document.
Apache openoffice 4.1.1
Windows 10
Fredrik Nilsson
 
Posts: 12
Joined: Fri Mar 29, 2019 9:51 am

Re: [Solved] Search and replace part of string with incremen

Postby MrProgrammer » Sat Apr 06, 2019 4:33 pm

MrProgrammer (a week ago) wrote:Select cell A2, Insert → Names → Define → Name → _ → Assigned To → $Ark1.$A$1:$Ark1.$A1
Fredrik Nilsson (yesterday) wrote:Cell A1 was selected when I defined name "_".
Aha! There is the trouble. I told you to select A2 when defining the name. You did not follow the directions.

Fredrik Nilsson wrote:It changed to 2 and even to other number without my control.
The definition is relative to the selected cell. After defining _ if you select a different cell, you will see a different Assigned To value. If you select A2, you will always see the same Assigned To velue.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3815
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: dumbuser, Google [Bot] and 13 guests