Need to display only portion of LIST selection

[beansinCA]

Using Data-Validation I have setup a list at D1 using source =$M$1:$M$4

The source data is: M1="100 Customer 1", M2="101 Customer 2", M3="102 Customer 3", M4="103 Customer 4"

When a user selects an item from the list I just want to display the left three characters of the string (i.e. 100 if first selection is made). Is this doable in Excel?

 

[Aladin Akyurek]

Looks like you want...

E1:

=LEFT(D1,3)

 

[beansinCA]

I can do that if there is no alternative but prefer to have the result show in D1

 

[pgc01]

Hi beansinCA

In that case I believe you have to use the Change Event of the worksheet and extract the first 3 characters of the value.

Hope this helps
PGC

 

[beansinCA]

Hi PGC,
I'm not sure what you mean by use Change Event. Can you provide a little more detail?

Thanks!

 

[pgc01]

Hi again

Paste this code in the module sheet (right-click on the sheet tab, choose view code, check that the highlighted module is the module of the sheet where D1 is, and paste this code).

Hope this helps
PGC

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$1" Then _
        Target.Value = Left(Target.Value, 3)
End Sub