Conver values into text

spaceman2155

New member
Joined
Nov 26, 2013
Messages
2
Reaction score
0
Points
0
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
CodeType
1Advertising
2Management Fee
3aLoan Interest
3bInsurance on Building
3cLife Assurance
4Environmental Waste Charges
5ESB
6Gas
7Tolls & fuel
8Phone
9Bank Charges
10Tenancy 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.


Thanks,
John
 

spaceman2155

New member
Joined
Nov 26, 2013
Messages
2
Reaction score
0
Points
0
Hello,

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.

Thanks,
John
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 
Top