Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

using hidden characters

using hidden characters

using hidden characters

Can I place a label in a cell, and have one of the characters remain hidden while viewed?

My situation is as follows;

On one sheet, I have a list of structures all labeled with numbers. On a seperate sheet, I type in the number I want, and use VLOOKUP to fill in the remaining results for each structure that I wish to print off.

My problem is that some structures were added and are labeled 14, 15, 15a, 16, 17, 17a, 18, ect. 15 and 15a are two different strucutres with different values, but when I pull them into my final sheet with the vlookup commands, I want them both to be displayed as 15

could I label my sturctures something like 15 and 15_ or 15* or 15# so that i can type 15* and vlookup would pull my data for 15*, but would be displayed as 15 and the * character would be hidden.

RE: using hidden characters


did you try the simplest way using the LEFT function?

The formula =LEFT(VLOOKUP(A7,B1:E4,1,0),2) where A7 is the lookup value (e.g. 15a) and the range B1:E4 contain the data (starting with struct codes: 15, 15a, 15*, 15_ etc), should return you always 15 as code.

Does it make sense to your needs?


RE: using hidden characters

In your example, is there any way that I can type in 15* in cell A7, vlookup would recognize it for other cells, but only 15 will be displayed in A7?

I basically want to know if I can type out 15*, 15_, and only have the 15 show. Or is there a formating style that will only display the first 2 characters?

RE: using hidden characters

In this case you probably need to write some VBA lines.
In the worksheet code i would write something like:


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 1 Then            ' if you are in Column A
    If ActiveCell.Value Like "*_" Or ActiveCell.Value Like "*a" Then
            MyVal = ActiveCell.Value
           ActiveCell.Value = Left(ActiveCell.Value, 2)
           [B7] = WorksheetFunction.VLookup(MyVal, [B1:E2], 2, 1)
           ' other statements using Myval as lookup value
    End If
  End If
End Sub

Hope it helps


RE: using hidden characters

Alternative to palusa's, I make the colorindex of the last characters equal to 2 (which is white):


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = [B1] Then
       Target.Characters(Start:=3, Length:=10).Font.ColorIndex = 2
    End If
End Sub


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: using hidden characters

Thanks, those ideas both help.

RE: using hidden characters

Joerd / palusa

I have not seen the [B7] =... and ...= [B1] notation used before. Is that a direct cell reference, sort of a short cut to where a named range could also be used?


Wheels within wheels / In a spiral array
A pattern so grand / And complex
Time after time / We lose sight of the way
Our causes can't see / Their effects.

RE: using hidden characters


yes, [A1] is the short form for Range("A1")

RE: using hidden characters

A little bit more elegant approach could be:
1\ your input column will be A
2\ make column B hidden
3\ columns C, D etc. will contain the VLOOKUPs' data referencing the lookup value in COLUMN B (e.g. in D9 =+VLOOKUP(B9;$D$1:$H$4;2;0))


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 1 Then                     ' I am in Column A
    With ActiveCell
        If IsEmpty(.Offset(0, 1).Value) Then  ' to be sure i'm inserting a
                                              ' new record
            .Offset(0, 1) = .Value
            If IsHere(ActiveCell.Value) Then
                .Value = Left(.Value, 2)
            End If
       End If
    End With
End If

End Sub

Public Function IsHere(V) As Boolean
A = Array("_", "a", "b", "c", "/", "+")
' don't include * or # as end char
IsHere = False
For I = 0 To UBound(A, 1)
    If V Like "*" & A(I) Then
        IsHere = True
        Exit Function
    End If
Next I
End Function

Function IsHere is used to check the termination character. You can customize the terminators just adding or modifying the elements of array A.
Don't use the characters * and # as terminators, otherwise the function is always True.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close