In Microsoft Excel, I wrote the following things now the cell C1 is showing !value#. Can somebody please solve this please? - St

admin管理员组

文章数量:1277385

Basically I tried the function CONCATENATE and also tried &, but both are not working.

Microsoft Excel is only showing !value#. How to bypass the problem of exceeding 255 characters?

Can somebody please tell me how to solve this?

I want to use more than 255 character. Is there anyway to use & functions to avoid this error?

This is the formula that I used is:

=HYPERLINK("=%22&c2&%22&text=A2&"&text="&b1, "send")

Basically I tried the function CONCATENATE and also tried &, but both are not working.

Microsoft Excel is only showing !value#. How to bypass the problem of exceeding 255 characters?

Can somebody please tell me how to solve this?

I want to use more than 255 character. Is there anyway to use & functions to avoid this error?

This is the formula that I used is:

=HYPERLINK("https://api.whatsapp/send?phone=%22&c2&%22&text=A2&"&text="&b1, "send")

• excel
• excel-2007

Share Improve this question Follow edited Feb 25 at 19:48 marc_s 755k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Feb 25 at 11:35 SamoiSamoi 1133 bronze badges 2

• Try this explanation on how to do this – PeterT Commented Feb 25 at 13:14
• 1 Upvote for using a ruler. – Neil T Commented Feb 25 at 13:36

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

The best way round the 255 character limit of the HYPERLINK function is not to use it.

Instead, use VBA to add the hyperlinks.

Prepare the hyperlink text for the VBA to process by using the TEXTJOIN function.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Using your whiteboard screenshot, it would look like this:

=TEXTJOIN("",TRUE,"https://api.whatsapp/send?phone=",A2,"&text=",B1)

Then add and run this VBA code.

Option Explicit

Private Sub Convert_to_Link()

    Dim rngCell As Range
    Dim rngSource As Range
    Dim rngTarget As Range
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    Set rngSource = ws.Range("C1:C4")
    Set rngTarget = ws.Range("D1:D4")
'Adjust this manually.  Better to set programatically

    rngTarget.ClearContents
'Clear the target range before processing

    For Each rngCell In rngSource
        If Left(rngCell.Value, 8) = "https://" Then
            ws.Hyperlinks.Add Anchor:=rngCell.Offset(0, 1), Address:=rngCell.Value, TextToDisplay:="Send"
        End If
    Next rngCell
'Loop through each cell in the source range and check if it starts with https://
'If it does, add it as a hyperlink in the cell to the right.

    With rngTarget
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .EntireColumn.AutoFit
    End With
'Format the target range

End Sub

Clicking on the Send hyperlink will open a browser window and link to whatsapp

本文标签：