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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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.
 
Upvote 0
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)"
 
Upvote 0
Yep, this is it:

=VLOOKUP('Price Selection'!$C$4,'Price Selection'!$B$8:$G$15,5,FALSE)
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top