[Solved] Manipulating cell content

[hardrott]

Hi. I've a cell content like "CAT1,CAT2,CAT3" and i want to rewrite this cell like "Cat1 > Cat2 > Cat3, Cat1 > Cat2, Cat1". Is this possible and how?

PS: " > " is not as a comparative operator & first letter of every word must be capital

[eeigor]

Remove the ambiguity in the example. It is unclear how generalized the example is. Give a fragment of real data.

[hardrott]

The cell contains a category tree.
CAT1 is main category, CAT2 is subcategory of CAT1 and CAT3 is subcategory of CAT2
For e.g. Books->English Books->Picture Books.
In the cell, it's written this way: BOOKS,ENGLISH BOOKS,PICTURE BOOKS
But i need it to be written this way: Books > English Books > Picture Books, Books > English Books, Books

Is it clear now?

[RoryOF]

So there might be spaces in the categories? Might there be more than one space in a category such as in "This is a very long category"? Might there also be quotes (single or double) and other punctuation characters.

All these will affect the parser that needs to be written.

[hardrott]

Except double quotes, other punctuation characters might apply (Like READING-WRITING BOOKS converts to Reading-Writing Books or JOHN CARPENTER'S THE THING converts to John Carpenter's The Thing) but the main point here is it's like CSV, wherever it finds a comma, it should split the string and rewrite in the pattern i need.

[Zizi64]

Please upload one or two ODF type sample file here with the "before"/after" state.

[hardrott]

Here is a sample file. I shared it as it is, did not translate to English.

[Zizi64]

Here is a sample without converting the uppercase of the first letters...

sampleZizi64.ods

(16.08 KiB) Downloaded 146 times

[hardrott]

Here is a sample without converting the uppercase of the first letters...

sampleZizi64.ods

Thank you so much, Zizi64..

I've gathered all your formulas in one but it became a mess Is there any way to simplify/beautify?

Code: Select all

= PROPER(LEFT(A2;SEARCH(",";$A2;1)-1))&" > "&PROPER(LEFT(LEFT((RIGHT($A2;LEN(A2)-SEARCH(",";$A2;1)));SEARCH(",";RIGHT($A2;LEN(A2)-SEARCH(",";$A2;1));1)-1);SEARCH(",";RIGHT($A2;LEN(A2)-SEARCH(",";$A2;1));1)-1))&" > "&PROPER(RIGHT($A2;LEN(RIGHT($A2;LEN(A2)-SEARCH(",";$A2;1)))-SEARCH(",";RIGHT($A2;LEN(A2)-SEARCH(",";$A2;1));1)))&", "&PROPER(LEFT(A2;SEARCH(",";$A2;1)-1))&" > "&PROPER(LEFT(LEFT((RIGHT($A2;LEN(A2)-SEARCH(",";$A2;1)));SEARCH(",";RIGHT($A2;LEN(A2)-SEARCH(",";$A2;1));1)-1);SEARCH(",";RIGHT($A2;LEN(A2)-SEARCH(",";$A2;1));1)-1))&", "&PROPER(LEFT(A2;SEARCH(",";$A2;1)-1))

[MrProgrammer]

Hi, and welcome to the forum.

I've gathered all your formulas in one but it became a mess

Experienced Calc users don't do that, precisely for the reason you stated. Monster formulas are difficult for people to understand, and may be inefficient if a result is evaluated multiple times when it should be evaluated once and stored in a cell. Your monster formula evaluates SEARCH(",";$A2;1) eleven times! Spread the calculation into as many cells as needed so each one has a simple formula. This makes finding problems much easier. You won't run out of cells. Each sheet has over a billion of them, and you can use as many sheets as will fit in memory. If you don't want to see the intermediate calculations put them in columns AAA, AAB, AAC, etc. Or hide those columns. Or put them on a different sheet. If it's tedious to create the intermediate calculations in distant or hidden cells or on another sheet, first use nearby cells then use Cut and Paste to move the calculations elsewhere. You must use Cut and not Copy.

I've attached a solution for the sample data which you provided. It uses only simple formulas. It does not need a macro.
ProTip: To understand expressions _ and ___ in column B see Insert → Names → Define.

202206301327.ods

(14.92 KiB) Downloaded 146 times

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

[Tutorial] Ten concepts that every Calc user should know

[Zizi64]

I've gathered all your formulas in one but it became a mess Is there any way to simplify/beautify?

Yes it is possible: for example by your own StarBasic Macros.

sampleZizi64macro.ods

(19.29 KiB) Downloaded 156 times

I just discovered a problem (bug?) in the PROPER+LOWER (and Basic LCASE) functions on the characters İ – i in LibreOffice 6.1.6 and AOO 4.1.7.

The macro code:

Code: Select all

Function MyConvert(TheStr as string) as string


	MyConvert = SplitProper(TheStr, ",", 1) +_
	" > " + SplitProper(TheStr, ",", 2) +_
	" > " + SplitProper(TheStr, ",", 3) +_
	", " + SplitProper(TheStr, ",", 1) +_
	" > " + SplitProper(TheStr, ",", 2) +_
	", " + SplitProper(TheStr, ",", 1)
End function



Function SplitProper(TheStr, SepChar, StrIndex)
 StrArray = DimArray( "a", "b", "c" )

	StrArray = Split(lcase(TheStr), SepChar)
	oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	sText = StrArray(StrIndex-1)
	arg = Array(sText)
	
 SplitProper = (oFunctionAccess.CallFunction( "PROPER", arg))

End Function

[Rafkus_pl]

Code: Select all

Function MyConvert(TheStr as string, InSepChar as string, OutSepChar as string, optional nr as byte) as string
 dim StrArray()as variant
 dim TheStr2 as string
 
  if IsMissing  (nr) or not(nr=1 or nr=2 or nr=0) then nr=3
  TheStr2 = ""

  TheStr = StrConv(TheStr, nr)
  StrArray = split(TheStr, InSepChar )   
  InSepChar = InSepChar & " "
 
  for i=UBound(StrArray) to LBound(StrArray) step -1
    ReDim Preserve  StrArray(i)
    TheStr2 =  TheStr2 &  iif(TheStr2 = "" , "" , InSepChar)  & Join(StrArray, OutSepChar)
  next 
  MyConvert = TheStr2
End function

=MYCONVERT(A3; ","; " > ")

[karolus]

Just seven lines of python for performing INPLACE selected CellRange:

Code: Select all

def main(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sel.DataArray = [[ perform(text) for text in row ] for row in sel.DataArray ]

def perform( pattern ):
    parts = pattern.title().split(",")
    return ", ".join([" > ".join(parts[:i]) for i, _ in enumerate(parts,1) ][::-1] )     

[RPG]

In Libreoffice I do it with this formula but ApacheOffice does not have the function regex

Code: Select all

=REGEX(PROPER(A1) ;"([^,]+),([^,]+),([^,]+)";"$1 > $2 > $3, $1 > $2 , $1")

The same regular expression and replace text can be used in ApacheOffice when you use the find and Replace. When you use Find and Replace be sure you have a copy of you data. When you are testing you can also use Ctrl-z. Ctrl-z is the same as Menu --> Edit --> Undo.
For me it was important: there I did understand more of the regular expressions

Code: Select all

([[:alnum:][:space:]-]{1,})\,([[:alnum:][:space:]-]{1,})\,([[:alnum:][:space:]-]{1,})
([[:alnum:][:space:]-]+)\,([[:alnum:][:space:]-]+)\,([[:alnum:][:space:]-]+)  The + is the same as {1,} for what we need
([[:alnum:][:space:]-]+),([[:alnum:][:space:]-]+),([[:alnum:][:space:]-]+)    We do not need the backslash for the comma
([^,]+),([^,]+),([^,]+)  

I did work slowly to the last line of code. It was also learning for me. Below I do try to give an explanation but it is difficult also for the language. For the explanation use also the help file for details. It is really possible that you characters in yours names who make it is not working. When you have also a comma in the name it not possible.

Before you can work with a regular expressions it is good to practice a little with normal words and you have to learn which checkboxes must be activated or not activated.
Be also aware make a copy of the original data there it destroys your original data. Make a copy in for your file or copy it in your document. When you test you can switch back to original content with Ctrl-z.


I think it is also possible when you use a regular expression in search and replace. Regular expression are explained in the help file. It is good to read them for the details there they are difficult to explain.
An regular are build up with little details.
we start with: [] Those two characters make it possible to search for characters we need. In your case we need:
[:alnum:] is for characters and numbers
[:space:] there is sometimes a space in the name
- You use this as a separator in a name
Now you get: [[:alnum:][:space:]-]
We want find and remember all the character just before the: ,
For all the characters we add: +
for remembering we surround it with: ()
for ending the search for a name we add: ,
you get for the first name: ([[:alnum:][:space:]-]+),

Now we have the expression for one name. we have expand it for three names
([[:alnum:][:space:]-]+),([[:alnum:][:space:]-]+),([[:alnum:][:space:]-]+)
There at the end there is no comma we can delete the last comma.

Now for replacing all in the good order:
$1 > $2 > $3 , $1 > $2 , $1
The number $1, $2 and $3 are related to the names who are found in the part who are surround with(). We can use the numbers as variables


Explanation for : ([^,]+),([^,]+),([^,]+)
The work is done with [^,] This searches for characters who are not a comma
Any single occurrence of a character, including Tab, Space and Line Break characters, that is not in the list of characters specified inclusive ranges are permitted. For example "[^a-syz]" matches all characters not in the inclusive range ‘a’ through ‘s’ or the characters ‘y’ and ‘z’.

[Zizi64]

=MYCONVERT(A3; ","; " > ")

What you have tried with this formula? My macro function MyConvert requires only one string as input parameter. All of others is located in the macro function.

[RPG]

What you have tried with this formula? My macro function MyConvert requires only one string as input parameter. All of others is located in the macro function

It is not clear to me if this was for me? But the reason I did try was more that I did more understand of Regulair Expression. I did have look more often to regular expression and did now understand some parts of it and now things as the numbers for the expression is clear to me. I did have looked to your macro before and the only reason I could not forget to find a solution.

If you mean that it does not help most people I do agree with you.

[Zizi64]

What you have tried with this formula? My macro function MyConvert requires only one string as input parameter. All of others is located in the macro function

It is not clear to me if this was for me?

Not. That was for Rafkus_pl's last post.

[hardrott]

@MrProgrammer
Thank you so much. I just did not want to display chunks but I also did not think of hiding cells, that's what I'll do now.

@Zizi64
Thank you sooo much. İ – i seems okay with me in AOO 4.1.7? Can you define the problem?

[Zizi64]

@Zizi64
Thank you sooo much. İ – i seems okay with me in AOO 4.1.7? Can you define the problem?

Ahh, sorry: i see now, that Rafkus_pl 's function "MyConvert()" is not same as "MyConvert()" recommended by me before.