EXCEL วิธี ตัดที่อยู่ยาวๆ ในแบบฟอร์ม ขึ้นบรรทัดใหม่ หรือ ใช้ประยุกต์ตัดข้อความที่ต้องการออก

Introduction

In this tutorial, we will explore how to break long addresses into new lines in Excel forms. This process can also be applied to extract specific text from longer strings. These skills are useful for organizing data, improving readability, and ensuring that forms appear neat and professional.

Step 1: Preparing Your Data

Before manipulating your data, ensure that your long addresses or text strings are in a single column in Excel.

• Open Excel and locate the column that contains the long addresses.
• Make a backup of your data to prevent accidental loss.

Step 2: Using the Formula to Break Text into New Lines

Excel has a way to insert line breaks within a cell by using a specific formula.

• Select the cell where you want the formatted address to appear.

• Enter the following formula:

  =SUBSTITUTE(A1, " ", CHAR(10))
  

  Explanation:

  • Replace A1 with the reference to the cell containing your long address.
  • The SUBSTITUTE function replaces spaces with line breaks (CHAR(10)).

• Press Enter. You may need to adjust the cell’s formatting to see the line breaks.

Step 3: Formatting Cells for Line Breaks

To ensure that the line breaks are displayed correctly, you need to format the cells.

• Right-click on the cell with the formula and select "Format Cells."
• Go to the "Alignment" tab.
• Check the box for "Wrap text."
• Click "OK."

Now, the address will appear on multiple lines within the same cell.

Step 4: Extracting Specific Text

If you want to extract specific parts of the address or text, you can use the LEFT, RIGHT, or MID functions.

• For example, to extract the first 10 characters:

  =LEFT(A1, 10)
  

• To extract characters starting from the 5th position for 10 characters:

  =MID(A1, 5, 10)
  

• Adjust the cell references and numbers according to your needs.

Practical Tips

• Always double-check the references in your formulas.
• Use "Find and Replace" (Ctrl + H) for bulk text changes if necessary.
• Test your formulas on a small set of data before applying them to larger datasets.

Common Pitfalls

• Forgetting to enable text wrapping can lead to confusion when the data appears all in one line.
• Incorrect cell references in your formulas can result in errors.

Conclusion

By following these steps, you can effectively break long addresses into multiple lines in Excel and extract specific text as needed. This tutorial equips you with essential techniques for managing and formatting data, enhancing both the clarity and presentation of your forms. For more complex data manipulation, consider exploring additional Excel functions or features like Power Query.