Results 1 to 3 of 3

Thread: Conver values into text

  1. #1

    Conver values into text

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have sheet 1 with a column "P" where the cells in that column may contain numbers "1, 2, 3a, 3b, 3c, 4, 5, 6, 7, 8, 9, 10" the cells will vary so P2 may contain "2, 3a, 10" and P3 may contain "2, 3a, 3b, 5, 6" and so on, also P4 may contain no values.

    I need a formula so if cell "P2" contains the values "2, 3a, 10" then the formula will look at a different Sheet 3 (see below) and pick out "Management Fee, Loan Interest and Tenancy Registration Fees" and combine this text in cell "AX2", The text if possible should have the code then a space the text and then a carriage return.

    Sheet 3 information below
    Code Type
    1 Advertising
    2 Management Fee
    3a Loan Interest
    3b Insurance on Building
    3c Life Assurance
    4 Environmental Waste Charges
    5 ESB
    6 Gas
    7 Tolls & fuel
    8 Phone
    9 Bank Charges
    10 Tenancy Registration Fees

    So what I am trying to achieve is that the cell "AX2" would be as follows:

    2 Management Fee
    3a Loan Interest
    10 Tenancy Registration Fees

    I will be copying this formula down about 1500 cells.

    I have tried split the values in one cell by "Text to Columns" and then I have used "=CONCATENATE(VLOOKUP(BB2,Sheet3!$A$2:$B$14,2,FALSE),VLOOKUP(BC2,Sheet3!$A$2:$B$14,2,FALSE),VLOOKUP(BD2,Sheet3!$A$2:$B$14 ,2,FALSE),VLOOKUP(BE2,Sheet3!$A$2:$B$14,2,FALSE),VLOOKUP(BF2,Sheet3!$A$2:$B$14,2,FALSE), VLOOKUP(BG2,Sheet3!$A$2:$B$14,2,FALSE),VLOOKUP(BH2,Sheet3!$A$2:$B$14,2,FALSE), VLOOKUP(BI2,Sheet3!$A$2:$B$14,2,FALSE))"

    if works great for a where all the values are used, but if I have two values in one row and the next row down I will have values, since some cells will be blank, this is giving N/A in the cell AX2, I am told I could try adding =concatenate(if(isna(vlookup..... but this is giving to many arguments and I cannot find the problem.


  2. #2

    I have just figured out how to do the above problem with alot of help from someone, but now I am wondering if it is possible to write a formula that will automatically convert cell values i.e. "1, 2, 3a, 3b, 3c, 4, 5, 6, 7, 8, 9, 10" to "Text to columns" command if you state the cell you want to begin at.


  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    If you are getting help on another forum, please place the link to that thread here, so that we know the solution status.

Posting Permissions

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