asked on

Excel VBA Display issue

Hi EE

I've had an amateurish attempt at writing a function in Excel that will take text, eg. "11.CP", remove the full stop and then leave the text preceding the full stop and after the full stop separated by char(10) which results in the two text strings being separated by a line when the cell is defined as having wrapped text.

When I use the function, it mostly displays the formula in the cell rather than the result, eg. "11" & Char(10) & "CP". If I copy and paste the format from a nearby cell, it works, but I don't think it updates if the original text were to change.

I hope someone can help though it is far from life threatening.

Many thanks
Ben

Rgonzo1971

Hi,

pls try

Function ReplaceDotWithChr10(txt As String) As String
ReplaceDotWithChr10 = Replace(txt, ".", Chr(10))
End Function

or as a formula
=SUBSTITUTE(A1,".",CHAR(10))

Regards

krishnakrkc

Select the range and run the code.

Option Explicit

Sub kTest()
    
    Dim s As String
    
    If TypeOf Selection Is Range Then
        With Selection
            s = "'" & .Parent.Name & "'!" & .Address
            .Value = Evaluate("if(len(" & s & "),substitute(" & s & ",""."",char(10))," & s & ")")
            .WrapText = True
        End With
    End If
    
End Sub

Open in new window

Kris

Ben Cheetham

ASKER

Many thanks for such a swift response. I apologise again for my cumbersome code and welcome your alternative.

Both options replace the "." but appear to leave a space. Neither put the second part of the original 'string' onto a second line.

Maybe it isn't Char(10) that I need?

What I need is for 10.HGF to be shown as

10
HGF

but all in the same cell.

I hope you can persevere with this. Thanks for your effort thus far.

Rgonzo1971

Like you said

when the cell is defined as having wrapped text.

a function will not be able to change that only a sub

Regards

Ben Cheetham

ASKER

Dear Rgonzo1971

I don't think I've been clear with what I need - I'm sorry.

I can set the cell to be centred and with text wrapping but I can't get the two parts of the original string to appear on two separate lines.

Here's an image of what I want to achieve with the function.

I hope you can keep trying.

Thanks
Ben

Rgonzo1971

See my example
EE20140806.xlsx

krishnakrkc

Hi

Have you tried my code ? It wraps the range, so it will show the cell content as you wanted.

Kris

Ben Cheetham

ASKER

Dear Kris

I've tried yours but it only puts the second part of the text on a second line if the cell isn't wide enough to fit the whole text. I need it to do this irrespective of the width of the cell.

Ideally, I'm looking for this as a function rather than a sub.

I am sorry not to have acknowledged your kind effort so far.

Ben

Ben Cheetham

ASKER

Dear Rgonzo1971

It doesn't wrap the text onto a second line unless the string is longer than the cell width. I need it to split the text onto two lines irrespective of the cell width.

Kind regards
Ben

Rgonzo1971

Could you send a sample?

I have no problem doing it

Ben Cheetham

ASKER

Wow!

I am currently on a MAC but I will now try on a PC and come back to you.

Ben Cheetham

ASKER

It works perfectly on a PC! Thank you very much. This will make a difference to the majority of users.

I am still unsure what to do for those using Office for Mac. Please let me know if you have any inspiration on this.

Thanks again. So simple but so clever. Brilliant.

Rgonzo1971

I've read that is some versions of Mac you have to use CHAR(13) instead of CHAR(10)

Rgonzo1971

if the info about mac is correct cannot verify

pls try

=SUBSTITUTE(A2,".",CHAR(IF(INFO("system")="mac",13,10)))

Ben Cheetham

ASKER

Hi again

CHAR(13) does indeed work on a Mac!

I am not sure how to adjust the VBA function (rather than the substitute version) to reflect this?

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ben Cheetham

ASKER

Exceedingly grateful for working so quickly towards a resolution!

Rgonzo1971

You could also try this one

Function ReplaceDotWithChr10(txt As String) As String
#If Mac Then
strChr = 13
#Else
strChr = 10
#End If
ReplaceDotWithChr10 = Replace(txt, ".", Chr(strChr))
End Function