How to get a partially bolded string in calc? [closed]

asked 2018-05-26 00:29:42 +0200

rudregues
11 ●1 ●1 ●4

updated 2020-07-29 12:01:34 +0200

flag of United Kingdom of Great Britain and Northern Ireland

20965 ●21 ●60 ●60 http://etmg.altervista...

In column name I have a list with hundreds of strings, each one with one or more bolded words. In column grade I have their respectives grades. I want to get all names with A in grade column. But I want to preserve bolded words.

My formula is: formula

The result I want is this: image description

But I always get this: image description

I searched functions that get bolded text, but didn't found any. The best I found was https://ask.libreoffice.org/en/questi... but I couldn't figure out how to use this in order to solve my problem.

EDIT: I found something interesting here https://stackoverflow.com/questions/4... but seems like the guy did some LO Basic programming which honestly I didn't understand a thing and he just wanted to bold the first word of the string...

EDIT2: After Liberty Belle's answer I updated my post to better clear what I really want.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-29 12:01:50.570329

Comments

This is completely different from your original post. This is not at all what you originally asked.

Liberty Belle ( 2018-05-26 16:14:53 +0200 )edit

VLOOKUP is overkill, at least in the example you have shown here. IF(A2="A";B2;"") is enough.

Jim K ( 2018-05-26 20:38:22 +0200 )edit

+1 Very well written question. Good example and research. There's nothing wrong with editing the question multiple times until it is clear, and it's good to edit the question in response to comments and answers as you have done.

Jim K ( 2018-05-26 21:25:44 +0200 )edit

Thanks for your tip and answer Jim K!

rudregues ( 2018-05-28 16:07:02 +0200 )edit

I completely disagree that's "there's nothing wrong with editing the question multiple times until it is clear, and it's good to edit the question in response to comments and answers as you have done". You wouldn't feel that way if you had wasted your time answering it only to find out he meant something completely different. And no apology! Please don't encourage people to be sloppy and careless with other people's time and attention. Really.

Liberty Belle ( 2018-05-31 21:50:00 +0200 )edit

"You wouldn't feel that way if you had wasted your time answering it only to find out he meant something completely different." I've had this happen many times before, and while it can be annoying, at the end of the day, the question gets answered and hopefully, it will help other people who come across it in the future. Sometimes it's hard to know how to write a question to avoid misinterpretation, and the only way is to wait until someone attempts an answer and then edit the question.

Jim K ( 2018-06-01 20:47:22 +0200 )edit

It's also important to make people feel welcome even if (especially if) they don't have a lot of experience on how to ask questions correctly. Granted, that link is not for this site, but I hope people on this site share those values.

Jim K ( 2018-06-01 21:12:49 +0200 )edit

"And no apology!" -- That seems incorrect. He wrote, "Sorry for this. When I said..." in the comment. I'm not sure what more he could have done once he realized the original question needed to be corrected.

Jim K ( 2018-06-01 21:24:35 +0200 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2018-05-26 20:37:14 +0200

Jim K

4276 ●8 ●27 ●55

updated 2018-05-26 21:22:11 +0200

It is impossible for cells containing formulas to be partially formatted. Only cells containing text may be partially formatted.

In the example file below, whenever the sheet content is changed, the macro will find any students with grade A and then copy the name and format to column D.

Sample file: Automatically Copy Formatting And Text.ods

Sub CellTextAndFormat(oEvent)
    If oEvent.getColumns().hasByName("D") Then Exit Sub
    oDoc = ThisComponent
    oSheet = oDoc.getSheets().getByIndex(0)
    oColumn = oSheet.getColumns().getByIndex(0)
    oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
    oRanges.insertByName("a", oColumn)
    oCells = oRanges.Cells.createEnumeration()
    oCells.nextElement()  ' skip headings row'
    While oCells.hasMoreElements()
        oCellGrade = oCells.nextElement()
        row = oCellGrade.CellAddress.Row
        If oCellGrade.getString() = "A" Then
            Call CopyForRow(row + 1)
        Else
            oCell = oSheet.getCellByPosition(3, row)
            oCell.setString("")
        End If
    Wend
End Sub

CopyForRow() is based on recorded dispatcher code that copies and pastes formatted text, similar to @LibertyBelle's answer.

For more information on LO Basic, see Andrew Pitonyak's macro document. Part of my code was adapted from section 6.14. Display all data in a column.

Note: My first idea was to use spreadsheet formulas to determine which rows to copy. However, this added additional columns and required a more complex macro. Moving the logic to the macro proved to be better.

edit flag offensive delete link

Comments

Thanks for such a comprehensive answer! I will try the macro you programmed and read your source on LO Basic

rudregues ( 2018-05-28 16:12:51 +0200 )edit

add a comment

answered 2018-05-26 06:40:03 +0200

Liberty Belle
226 ●5 ●7 ●14

Copy the cells from column name and then Edit > Paste Special into the new column. In the dialog box that pops up, select Text and Formats.

image description

You would really benefit from reading the online documentation https://documentation.libreoffice.org... to learn the program. It will save you a lot of time in the end.

You get points for doing lots of research though!

image description

edit flag offensive delete link

Comments

I appreciate your answer, but it's not really what I need. Maybe I used the wrong words to say what I really need. When I say in the title "get" I mean after an operation like vlookup etc but I think I messed things up by using copied name in one of the columns. I'll update my post to better reflect my need.

rudregues ( 2018-05-26 15:21:18 +0200 )edit

I answered the question that was asked by you. You didn't "use the wrong words" in your original post. You asked an entirely different question. You left out vital information. Please take the time to fully explain what your issue is before posting. I don't appreciate wasting my time and neither does anyone else here.

Liberty Belle ( 2018-05-26 16:16:33 +0200 )edit

Sorry for this. When I said "get" partially bolded strings I was referring to "retrieve" it including bolded words after some operation. I thought it was clear, I just perceived it was ambiguous after your answer. Or even worse, maybe a totally different question! I'm a Libreoffice user for many years and never posted any question because I always search the web the deepest I can. This time I didn't found and posted my first question.

rudregues ( 2018-05-26 16:35:18 +0200 )edit

The official documentation itself uses "get" in the same meaning I used: "Formulas are equations using numbers and variables to get a result." Source: Introduction of Chapter 7 - Using Formulas and Functions, page 192 - LibreOffice 4.1 Calc Guide, avaiable here https://documentation.libreoffice.org...

Again, sorry for the confusion.

rudregues ( 2018-05-26 16:38:31 +0200 )edit

Your original post said nothing about a formula to combine two different columns. You asked how to copy partial bolding from one column to another. That was all.

Liberty Belle ( 2018-05-26 17:05:00 +0200 )edit

add a comment

How to get a partially bolded string in calc? [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-29 12:01:50.570329

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

How to get a partially bolded string in calc? [closed] edit

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-07-29 12:01:50.570329

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

How to get a partially bolded string in calc? [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-29 12:01:50.570329