Go Premium for a chance to win a PS4. Enter to Win

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

Show only first character in cell

Hi,

If I have a cell in Excel, that contains X3, is there a way to just show X in the cell?

The number 3 should still bee in the cell, but not be shown.

Thanks in regards.
0
DennisPedersen
Asked:
DennisPedersen
1 Solution
 
Saqib Husain, SyedEngineerCommented:
There are only two ways I can think of.

- make the column width narrow enough to displace the 3 to the next column and then put something in the next column so that the 3 is hidden

- use a shape which has a color which is the same as the background to cover the 3.

But both methods are not flexible to changes to the cell unless it is always x and a number.
0
 
ravindran_eeeCommented:
You can put this formula in another cell and hide the original one. Then drag the formula to rest of the cells so that the formula will apply for the entire column.

LEFT(A1,1)
0
 
DennisPedersenAuthor Commented:
Okay, I have figured out the solutions you are writing, I was looking for maybe something VBA, that could do the trick.

Maybe I should go in something conditional formating.
0
Independent Software Vendors: 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!

 
redmondbCommented:
Hi, Dennis.

The Hidden_Characters macro (below and attached) changes the colour of the second and subsequent characters to white for all cells in the active cell's column. Few things...
(1) It only works for strings.
(2) I was lazy and changed the colour to white. If the idea works for you, I can change the macro to use each cell's fill colour.
Sub Hidden_Characters()
Dim xLast_Row As Long
Dim xThis_Col As Long
Dim i As Long

Sheets("Sheet1").Activate

xThis_Col = ActiveCell.Column

xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
If xLast_Row = 0 Then
    MsgBox ("Empty sheet. Run cancelled.")
    Exit Sub
End If

For i = 1 To xLast_Row
    
    On Error Resume Next
        If Len(Cells(i, xThis_Col)) > 1 _
            Then Cells(i, xThis_Col).Characters(Start:=2, Length:=Len(Cells(i, xThis_Col)) - 1).Font.Color = 16777215
    On Error GoTo 0

Next

End Sub

Open in new window

Regards,
Brian.Hidden-Characters.xlsm
0
 
DennisPedersenAuthor Commented:
Hi Brian,

Thanks for the solution. It looks nice, but I'm having a problem with the solution. The single letter remaining should be centered in the column. But when the rest of the characters still is in the cell, it coult be a little difficult.
0
 
redmondbCommented:
Dennis,

Oh dear, that's a significant addition.

I need more information about the data...
    - are all the cells the same length?
    - besides displaying it, what else will you do with the data?

Thanks,
Brian.
0
 
DennisPedersenAuthor Commented:
Hi Brian,

The cells could contain D, X, N, D3, X3 and N3

When the cells contains D3, X3 and N3 the cell should display D, X and N, but it should contain  D3, X3 and N3.

The cells should afterwards be used to manpulate with conditional formatting.

Does this make sence?
0
 
redmondbCommented:
Dennis,

Thanks, that's easy...Hidden-Characters-V2.xlsmRegards,
Brian.
0
 
DennisPedersenAuthor Commented:
Hi Brian,

Thanks a lot, it looks like it working great :-)

But I'm not able to se where you have created the code. Could you please guide me?
0
 
redmondbCommented:
Dennis,

It was your suggestion - it's all done by Conditional Formatting.

Regards,
Brian.
0
 
byundtCommented:
Brian,
That was awfully clever!

Brad
0
 
redmondbCommented:
Thanks, Brad! <blush>
0
 
DennisPedersenAuthor Commented:
Hi Brian,

The solution worked perfect for me. Thans a lot for your time.
0
 
redmondbCommented:
Thanks, Dennis. Glad to help.
0

Featured Post

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!

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