[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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
0
Ben Cheetham
Asked:
Ben Cheetham
  • 8
  • 8
  • 2
1 Solution
 
Rgonzo1971Commented:
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
0
 
krishnakrkcCommented:
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
0
 
Ben CheethamAuthor Commented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rgonzo1971Commented:
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
0
 
Ben CheethamAuthor Commented:
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.

EE2.png
I hope you can keep trying.

Thanks
Ben
0
 
Rgonzo1971Commented:
See my example
EE20140806.xlsx
0
 
krishnakrkcCommented:
Hi

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

Kris
0
 
Ben CheethamAuthor Commented:
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
0
 
Ben CheethamAuthor Commented:
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
0
 
Rgonzo1971Commented:
Could you send a sample?

I have no problem doing it

EE
0
 
Ben CheethamAuthor Commented:
Wow!

I am currently on a MAC but I will now try on a PC and come back to you.
0
 
Ben CheethamAuthor Commented:
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.
0
 
Rgonzo1971Commented:
I've read that is some versions of Mac you have to use CHAR(13) instead of CHAR(10)
0
 
Rgonzo1971Commented:
if the info about mac is correct cannot verify

pls try

=SUBSTITUTE(A2,".",CHAR(IF(INFO("system")="mac",13,10)))
0
 
Ben CheethamAuthor Commented:
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?
0
 
Rgonzo1971Commented:
Hi,

pls try

Function ReplaceDotWithChr10(txt As String) As String
On Error Resume Next
strOS = Application.OperatingSystem
On Error GoTo 0
If strOS Like "*Windows*" Then
    strChr = 10
Else
    strChr = 13
End If
ReplaceDotWithChr10 = Replace(txt, ".", Chr(strChr))
End Function


Regards
0
 
Ben CheethamAuthor Commented:
Exceedingly grateful for working so quickly towards a resolution!
0
 
Rgonzo1971Commented:
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now