×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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

 Forum Search FAQs Links MVPs

## using hidden characters

(OP)
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.
Replies continue below

### RE: using hidden characters

Hi,

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?

_LF

### RE: using hidden characters

(OP)
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:

#### CODE

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

_LF

### RE: using hidden characters

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

#### CODE

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

Cheers,
Joerd

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

### RE: using hidden characters

(OP)
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?

Thanks

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

A2D,

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))

#### CODE

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.

HTH.

_LF

#### 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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.