Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: text replacing

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Detroit
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Went through the first 15 pages or so until my eyes started to hurt, couldn't find an answer, although it probably is in here somewhere. Anyway, here is my situation.

    I have 2 columns, 1st column list initials of person, 2nd column lists persons full name. I want to be able to enter into another cell the initials, and have it give me the person's name in that cell.

    i.e. I punch in 'jd' ... when I hit enter, it replaces 'jd' with 'John Doe'

    Thanks for any help you all can provide.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,426
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    try Lookup or Vlookup

    With Lookup info must be in ascending order.

    Vlookup is used most often. See Help.

    With a table named rL

    ABC ABC info
    DEF DEF info

    assuming code in A2

    =vlookup(a2,rL,2,0)or =vlookup(a2,rL,2,False)

    the 2 means look in second column
    the o or False means you want an exact match

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Northern Ireland
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just to add to Dave's answer, If 2 or more people have the same initials then Vlookup will only return the name for the first set of initials it finds.

  4. #4
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-30 11:44, macdonaldf wrote:
    Went through the first 15 pages or so until my eyes started to hurt, couldn't find an answer, although it probably is in here somewhere. Anyway, here is my situation.

    I have 2 columns, 1st column list initials of person, 2nd column lists persons full name. I want to be able to enter into another cell the initials, and have it give me the person's name in that cell.

    i.e. I punch in 'jd' ... when I hit enter, it replaces 'jd' with 'John Doe'

    Thanks for any help you all can provide.
    If I understand the question correctly, macdonaldf wants to input initials in a cell and get the entire name in the same cell.
    Can it be done by vlookup?
    Eli

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    If I understand the question correctly, macdonaldf wants to input initials in a cell and get the entire name in the same cell.
    Can it be done by vlookup?
    Eli
    No, you'd need either an extra column if you wanted to use VLookup or a possibly very long Change macro, if you wanted it in the same cell.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,426
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry if Vlookup is not appropriate.
    I read the part about 2 columns but I guess I did not read the part about the "same" cell.

    With the millions of cells available, I wonder whey 2 cells cannot be used.
    The code column could always be hidden later.

    You could also look at defined names.

    [ This Message was edited by: Dave Patton on 2002-03-30 12:52 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could insert this code in the sheets own module (rightclick on the sheettab, choose view code)
    Whenever you type/change D2 it will make a vlookup in A1:B100 and return the value from column B. This is not perfect and personally i would do like the others suggested.

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo finished
    If Target.Address = "$D$2" Then
    Target.Value = WorksheetFunction.VLookup(Target.Value, Range("A1:b100"), 2, 0)
    End If
    finished:
    End Sub

    regards Tommy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •