| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Text spacing in drop down lists
Hello,
I have a drop down list that contains clinic name/address/city/st/zip/phone# in a merged cell. Is there anyway to format the spacing so that it will look like a proper ship-to address? Thank you Cathy |
|
#2
|
|||
|
|||
|
In your source list, press Alt + Enter where you want the lines to break
Then, in the cell that contains the dropdown list, choose FormatCells On the Alignment tab, add a check mark to Wrap Text, and click OK In the dropdown list, you'll see a box character where the line breaks are, but they won't appear in the cell. Cathy Landry wrote: Hello, I have a drop down list that contains clinic name/address/city/st/zip/phone# in a merged cell. Is there anyway to format the spacing so that it will look like a proper ship-to address? Thank you Cathy -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#3
|
|||
|
|||
|
If I have 1000 lines I will need to do that for each one?
"Debra Dalgleish" wrote: In your source list, press Alt + Enter where you want the lines to break Then, in the cell that contains the dropdown list, choose FormatCells On the Alignment tab, add a check mark to Wrap Text, and click OK In the dropdown list, you'll see a box character where the line breaks are, but they won't appear in the cell. Cathy Landry wrote: Hello, I have a drop down list that contains clinic name/address/city/st/zip/phone# in a merged cell. Is there anyway to format the spacing so that it will look like a proper ship-to address? Thank you Cathy -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#4
|
|||
|
|||
|
In the source list, is each address all in one cell, or is it in several
columns? If it's in columns, that you've concatenated for the dropdown, e.g.: =A2&", " & B2 you could use the CHAR(10) function instead of commas, e.g. =A2&CHAR(10)&B2 Cathy Landry wrote: If I have 1000 lines I will need to do that for each one? "Debra Dalgleish" wrote: In your source list, press Alt + Enter where you want the lines to break Then, in the cell that contains the dropdown list, choose FormatCells On the Alignment tab, add a check mark to Wrap Text, and click OK In the dropdown list, you'll see a box character where the line breaks are, but they won't appear in the cell. Cathy Landry wrote: Hello, I have a drop down list that contains clinic name/address/city/st/zip/phone# in a merged cell. Is there anyway to format the spacing so that it will look like a proper ship-to address? Thank you Cathy -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#5
|
|||
|
|||
|
Yes, they are all in one cell.
Thank you! "Debra Dalgleish" wrote: In the source list, is each address all in one cell, or is it in several columns? If it's in columns, that you've concatenated for the dropdown, e.g.: =A2&", " & B2 you could use the CHAR(10) function instead of commas, e.g. =A2&CHAR(10)&B2 Cathy Landry wrote: If I have 1000 lines I will need to do that for each one? "Debra Dalgleish" wrote: In your source list, press Alt + Enter where you want the lines to break Then, in the cell that contains the dropdown list, choose FormatCells On the Alignment tab, add a check mark to Wrap Text, and click OK In the dropdown list, you'll see a box character where the line breaks are, but they won't appear in the cell. Cathy Landry wrote: Hello, I have a drop down list that contains clinic name/address/city/st/zip/phone# in a merged cell. Is there anyway to format the spacing so that it will look like a proper ship-to address? Thank you Cathy -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#6
|
|||
|
|||
|
You could try the following in a copy of your data:
Select the column of names/addresses Choose EditReplace In the Find What box, type a comma In the Replace With box, hold the Alt key, and on the number keypad type: 0010 (you won't see anything in the Replace With box) Click the Replace All button Click OK to confirm the replacement Click Close Cathy Landry wrote: Yes, they are all in one cell. Thank you! "Debra Dalgleish" wrote: In the source list, is each address all in one cell, or is it in several columns? If it's in columns, that you've concatenated for the dropdown, e.g.: =A2&", " & B2 you could use the CHAR(10) function instead of commas, e.g. =A2&CHAR(10)&B2 Cathy Landry wrote: If I have 1000 lines I will need to do that for each one? "Debra Dalgleish" wrote: In your source list, press Alt + Enter where you want the lines to break Then, in the cell that contains the dropdown list, choose FormatCells On the Alignment tab, add a check mark to Wrap Text, and click OK In the dropdown list, you'll see a box character where the line breaks are, but they won't appear in the cell. Cathy Landry wrote: Hello, I have a drop down list that contains clinic name/address/city/st/zip/phone# in a merged cell. Is there anyway to format the spacing so that it will look like a proper ship-to address? Thank you Cathy -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#7
|
|||
|
|||
|
It worked perfectly!!
Thank you so much! "Debra Dalgleish" wrote: You could try the following in a copy of your data: Select the column of names/addresses Choose EditReplace In the Find What box, type a comma In the Replace With box, hold the Alt key, and on the number keypad type: 0010 (you won't see anything in the Replace With box) Click the Replace All button Click OK to confirm the replacement Click Close Cathy Landry wrote: Yes, they are all in one cell. Thank you! "Debra Dalgleish" wrote: In the source list, is each address all in one cell, or is it in several columns? If it's in columns, that you've concatenated for the dropdown, e.g.: =A2&", " & B2 you could use the CHAR(10) function instead of commas, e.g. =A2&CHAR(10)&B2 Cathy Landry wrote: If I have 1000 lines I will need to do that for each one? "Debra Dalgleish" wrote: In your source list, press Alt + Enter where you want the lines to break Then, in the cell that contains the dropdown list, choose FormatCells On the Alignment tab, add a check mark to Wrap Text, and click OK In the dropdown list, you'll see a box character where the line breaks are, but they won't appear in the cell. Cathy Landry wrote: Hello, I have a drop down list that contains clinic name/address/city/st/zip/phone# in a merged cell. Is there anyway to format the spacing so that it will look like a proper ship-to address? Thank you Cathy -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#8
|
|||
|
|||
|
You're welcome! Thanks for letting me know that it worked.
Cathy Landry wrote: It worked perfectly!! Thank you so much! "Debra Dalgleish" wrote: You could try the following in a copy of your data: Select the column of names/addresses Choose EditReplace In the Find What box, type a comma In the Replace With box, hold the Alt key, and on the number keypad type: 0010 (you won't see anything in the Replace With box) Click the Replace All button Click OK to confirm the replacement Click Close Cathy Landry wrote: Yes, they are all in one cell. Thank you! "Debra Dalgleish" wrote: In the source list, is each address all in one cell, or is it in several columns? If it's in columns, that you've concatenated for the dropdown, e.g.: =A2&", " & B2 you could use the CHAR(10) function instead of commas, e.g. =A2&CHAR(10)&B2 Cathy Landry wrote: If I have 1000 lines I will need to do that for each one? "Debra Dalgleish" wrote: In your source list, press Alt + Enter where you want the lines to break Then, in the cell that contains the dropdown list, choose FormatCells On the Alignment tab, add a check mark to Wrap Text, and click OK In the dropdown list, you'll see a box character where the line breaks are, but they won't appear in the cell. Cathy Landry wrote: Hello, I have a drop down list that contains clinic name/address/city/st/zip/phone# in a merged cell. Is there anyway to format the spacing so that it will look like a proper ship-to address? Thank you Cathy -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text to Columns from drop down list update | Excel Discussion (Misc queries) | |||
| Formulas dealing with text data | Excel Worksheet Functions | |||
| Data Validation - Drop down lists - if then? | Excel Discussion (Misc queries) | |||
| Can different drop down lists be displayed depending on the value. | Excel Discussion (Misc queries) | |||
| How do I change the appearance of my drop down lists? | Excel Worksheet Functions | |||
Linear Mode
