Using VLOOKUP, turn one cell with multiple lines of text into one line of text

Joshu

New Member
Joined
Jul 5, 2010
Messages
5
Hi,

I am doing up a fairly basic but lengthy pricelist for our business. Each Sheet has a different product range with a List & Nett Price.

The table headings go:
A B C D E F
| -----------------------------------------------------------------
1 | Part # | Detail 1 | Detail 2 | Detail 3 | List Price | Nett Price
| (Ex GST) (Ex GST)
|------------------------------------------------------------------
The 'Nett Price' heading is imported from one main sheet in the same workbook using a VLOOKUP formula (as it can then be changed to Nett Price/List Price/Inc GST depending on the customer group).

Can somebody please tell me how I can use a VLOOKUP formula to do the same thing BUT instead of returning two lines to a single cell, have the data be returned on one single line. I want all of the text but there is one section of the pricelist where the format is changed slightly and I need it all on the one line:

FROM:

Nett Price ------to------> Nett price (Ex GST)
(Ex GST)

Help anyone! :confused: :confused:. Thanks in advance!
 

Excelestial

Well-known Member
Joined
Jan 2, 2009
Messages
828
If the formula is using the "concatenate" or "&" with the "Char(10)" (which is a carriage return), remove the "Char(10)". It would help if you could provide the formula. It could very well be that your particular cell in the header row is formatted to autowrap in which case you'd simply turn it off from your toolbar.
 

Joshu

New Member
Joined
Jul 5, 2010
Messages
5
Hi Excelestial,

Thanks for the reply but I am a fairly simple minded Excel user and you have kind of lost me there!

The formula is as follows:

=VLOOKUP('Price Selection'!$C$4,'Price Selection'!$B$8:$G$15,5,FALSE)

Price Selection being the sheet where the discount structures are held. Contained in cell C4 is a drop-down list of different structures ie. Retail Price, Trade Price, Account less 50% etc.

The data from the drop down list is located in B8:G15 and adjacent to those cells, as you can see from the table array in the formula, is the Heading Title (eg. Nett Price.......) which I am trying to get onto one line only.

Its tricky because I need both. In some places I only have enough room to fit the "Nett Price (Ex GST)" on two lines, but here I need to save space by having it on one line.

I hope this makes sense to you (or anyone)!

Thanks.
 

Joshu

New Member
Joined
Jul 5, 2010
Messages
5
Oh btw I tried removing the 'Wrap Text' option in the Format Cells menu and it did work, however it gave me a little box with a question mark inbetween the "Nett Price" and "(Ex GST)"
 

Excelestial

Well-known Member
Joined
Jan 2, 2009
Messages
828
Can you copy the formula onto the board so I can take a look?
 

Joshu

New Member
Joined
Jul 5, 2010
Messages
5
Yep, this is it:

=VLOOKUP('Price Selection'!$C$4,'Price Selection'!$B$8:$G$15,5,FALSE)
 

Excelestial

Well-known Member
Joined
Jan 2, 2009
Messages
828
OH...disregard that last request...I found where you posted the formula. The results of the VLOOKUP will come in as they are found. And so I'm wondering if there is a special character in your lookup array (i.e., Char(10) or carriage return). Try this formula with your 'wrap text' on the toolbar turned off:

=Clean(VLOOKUP('Price Selection'!$C$4,'Price Selection'!$B$8:$G$15,5,FALSE))
 

Forum statistics

Threads
1,081,840
Messages
5,361,618
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top