Show only first character in cell


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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Ravindran GopinathanCommented:
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.

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.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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


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


End Sub

Open in new window

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.

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?

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?

Thanks, that's easy...Hidden-Characters-V2.xlsmRegards,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?

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

byundtMechanical EngineerCommented:
That was awfully clever!

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

The solution worked perfect for me. Thans a lot for your time.
Thanks, Dennis. Glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.