Text to Column converting is an important thing to in Excel due to almost all types of raw data need to collect from others software or Web Applications which was mostly unstructured to make a report on it. In this article you will learn how can you convert a cell into Columns that contains Text with "Enter" and ";" character.
Step by Step:
Assume that you have below data:
Now you want to convert the data like below:
(1) To do this, first of all make sure that, your B1 and C1 cell has set to Wrap Text. If not then Click on Home | Wrap Text to activate it.
(2) In B1 cell use the below formula to collect data before ";" character:
This formula, took the LEFT mentioned numbers of characters found with FIND and matched character.
(3) Now to collect the value after ";" use the below formula in C1 cell:
This formula will collect data from right where ";" has found.
Step by Step:
Assume that you have below data:
Image 1: Sample Data
Now you want to convert the data like below:
Image 2: Converted data
(1) To do this, first of all make sure that, your B1 and C1 cell has set to Wrap Text. If not then Click on Home | Wrap Text to activate it.
(2) In B1 cell use the below formula to collect data before ";" character:
=LEFT(A1,FIND(";",A1)-1)
This formula, took the LEFT mentioned numbers of characters found with FIND and matched character.
(3) Now to collect the value after ";" use the below formula in C1 cell:
=RIGHT(A1,LEN(A1)-FIND(";",A1))
This formula will collect data from right where ";" has found.