Solved Bold Partial text string in excel

February 4, 2016 at 23:11:01
Specs: Windows 7
I using the CONCATENATE function to merge multiple columns with some text and carriage returns. The result is being pasted as values in the next column. The result will look like this:

Section 1-1

Y = -77.687

X = (-3.454,13.500)

I want the first line of the result to be bold. I dont want to use code as I'm not comfortable with it.

Can anyone please help find a way in which I can use a formula/ conditional formatting/ replace or other non-code options to make the text string bold AFTER PASTING IT AS VALUES.

PS: The operation needs to be carried out in approx 1000 cells and every time the first line will be "Section ..." ( the "1-1" part may/may not change)

Thanks in advance!

See More: Bold Partial text string in excel

Report •

February 5, 2016 at 08:17:12
✔ Best Answer
It would help if we knew what formula(s) you are using to create this layout.

If you could provide an example of the Input data along with the formula that applies to that data, we would have a better idea of exactly what you are doing. From there we can see if there is something that can be done prior to the Paste Values operation in order to achieve your goal. I'm not saying that it can be done that way, but until I understand your entire process I don't want to say that it can or can't be done without code.

You have entered into kind of a grey area within Excel. While Excel does indeed have some text related functions, they are there (IMO) mainly as a convenience. Excel is "calculation based" application, not a word processing application. Many people try to force it into the word processing arena but it doesn't always fit well.

That doesn't mean that there aren't ways to get text operations done, just that, as you have found, it isn't always straight forward.

Regarding your comment "I don't want to use code as I'm not comfortable with it."...

It may be time to step out of your comfort zone. VBA code is a very powerful tool in it's own right and sometimes it is the only tool available for some tasks.

While I'm not saying that VBA is the only solution for this specific task, it is a viable option and can be done with a single instruction inside a loop.

The following code, while perhaps appearing "uncomfortable", is really pretty simple when explained in "English".

The For-Next loop counts from 1 to 1000 creating a variable [rwNum] that is used as a Row Number. i.e. Each time it loops, it refers to the next cell in Column A:

"A" & 1, "A" & 2, etc.

The single instruction inside the loop determines the position of the Carriage Return [Chr(10)] and then Bolds all characters from position 1 to the position of the Carriage Return.

What you end up with is:

Section 1-1

Y = -77.687

X = (-3.454,13.500)

Here is the code that does that:

Sub BoldOneLine()
  For rwNum = 1 To 1000
    Range("A" & rwNum).Characters(1, InStr(1, Range("A" & rwNum), _
           Chr(10))).Font.Bold = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question