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

[Joshu]

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! . Thanks in advance!

 

[Excelestial]

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]

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]

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]

Can you copy the formula onto the board so I can take a look?

 

[Joshu]

Yep, this is it:

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

 

[Excelestial]

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))

 

[Joshu]

You are my hero

It worked! Thanks heaps.

 

[Excelestial]

Aw shucks. lol

You're very welcome.