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

#### Joshu

##### New Member
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.

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

##### Well-known Member
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
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
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
Can you copy the formula onto the board so I can take a look?

#### Joshu

##### New Member
Yep, this is it:

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

#### Excelestial

##### Well-known Member
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

##### New Member
You are my hero

It worked! Thanks heaps.

#### Excelestial

##### Well-known Member
Aw shucks. lol

You're very welcome.

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

### 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...